Integers behaving like strings

[night_writer]

I hope someone can help me find the reason for this behavior.

I am using a query to run a report that needs to capture status at the
end of the month, so I need to exclude records that might have been
added later than the month end I'm interested in.

I do this by setting a month and year on a form named Splash. The
month field, [cbxMonth], is a combo box with column 1 being MonthID as
an integer and column 2 being the month name as a string. The bound
column is column 1. (I started this as a value list, and then tried
basing it on a table just in case there was something wrong with the
way I defined this field. Neither method solved my problem.)

My query selects records from a table based on a date in the record
[dteSurvey]. The field is formatted as a date. My query field is an
expression: Month: Month([dteSurvey]) with the criteria: <=[Forms]!
[Splash]![cbxMonth].

This is what's driving me nuts. I have an entry in the data base of
12/1/2010. When I set [cbxMonth] to any month except January, the
December entry is included in the results. If I set [cbxMonth] to
January, the December results are excluded. If I remove the criteria
formula and just put a number in, like "4" (without quotes), the
December results are excluded as they should be.

My query seems to be treating Month([dteSurvey]) like an string and
yet as far as I can tell, there is nothing I'm doing that would turn
that number into a string.

I would greatly appreciate any hints anyone might have as to how to
get my data treated as the correct data type.

Thanks!
Alice

 

[J_Goddard via AccessMonster.com]

Hi -

First - this may be just semantics, but you stated that "The field is
formatted as a date". OK, but what is the data type of the field in the
table - is it Date/time or string?

Secondly - how can you be sure what date 12/1/2010 represents? Is it
December 1 (USA) or January 12 (more or less everywhere else)? In abiguous
cases like this, the interpretation used by the Month() function (when its
argument is a string) depends on the settings for date in the Regional and
Language options of the Windows Control Panel, so you might want to check
which you are using.

you could also try changing your query criteria to <= val([Forms]![Splash]!
[cbxMonth])

force it to an integer.

HTH

John

I hope someone can help me find the reason for this behavior.

I am using a query to run a report that needs to capture status at the
end of the month, so I need to exclude records that might have been
added later than the month end I'm interested in.

I do this by setting a month and year on a form named Splash. The
month field, [cbxMonth], is a combo box with column 1 being MonthID as
an integer and column 2 being the month name as a string. The bound
column is column 1. (I started this as a value list, and then tried
basing it on a table just in case there was something wrong with the
way I defined this field. Neither method solved my problem.)

My query selects records from a table based on a date in the record
[dteSurvey]. The field is formatted as a date. My query field is an
expression: Month: Month([dteSurvey]) with the criteria: <=[Forms]!
[Splash]![cbxMonth].

This is what's driving me nuts. I have an entry in the data base of
12/1/2010. When I set [cbxMonth] to any month except January, the
December entry is included in the results. If I set [cbxMonth] to
January, the December results are excluded. If I remove the criteria
formula and just put a number in, like "4" (without quotes), the
December results are excluded as they should be.

My query seems to be treating Month([dteSurvey]) like an string and
yet as far as I can tell, there is nothing I'm doing that would turn
that number into a string.

I would greatly appreciate any hints anyone might have as to how to
get my data treated as the correct data type.

Thanks!
Alice

 

[Jerry Whittle]

1. Show use the SQL for the query.

2. 12/1/2010 -- Is that DD/MM/YYYY or MM/DD/YYYY?

3. "The field is formatted as a date." Does this mean that the field is a
Date/Time data type in the table OR it's a text field that looks like a date?

 

[night_writer]

Thank you, thank you!

The val() function worked. (I had tried this before in the equasion
but got an error message. Using it in the criteria made it work)

To answer your other questions, the table field data type is Date/Time
(Short Date), and so is the entry form's corresponding field. Dates
are USA style MM/DD/YYYY.

Thanks again. I was giving up hope!

Alice

Hi -

First - this may be just semantics, but you stated that "The field is
formatted as a date".  OK, but what is the data type of the field in the
table - is it Date/time or string?

Secondly - how can you be sure what date 12/1/2010 represents?  Is it
December 1 (USA) or January 12 (more or less everywhere else)?  In abiguous
cases like this, the interpretation used by the Month() function (when its
argument is a string)  depends on the settings for date in the Regionaland
Language options of the Windows Control Panel, so you might want to check
which you are using.

you could also try changing your query criteria to <= val([Forms]![Splash]!
[cbxMonth])

force it to an integer.

HTH

John

I hope someone can help me find the reason for this behavior.

I am using a query to run a report that needs to capture status at the
end of the month, so I need to exclude records that might have been
added later than the month end I'm interested in.

I do this by setting a month and year on a form named Splash. The
month field, [cbxMonth], is a combo box with column 1 being MonthID as
an integer and column 2 being the month name as a string. The bound
column is column 1. (I started this as a value list, and then tried
basing it on a table just in case there was something wrong with the
way I defined this field. Neither method solved my problem.)

My query selects records from a table based on a date in the record
[dteSurvey]. The field is formatted as a date. My query field is an
expression: Month: Month([dteSurvey]) with the criteria: <=[Forms]!
[Splash]![cbxMonth].

This is what's driving me nuts. I have an entry in the data base of
12/1/2010. When I set [cbxMonth] to any month except January, the
December entry is included in the results. If I set [cbxMonth] to
January, the December results are excluded. If I remove the criteria
formula and just put a number in, like "4" (without quotes), the
December results are excluded as they should be.

My query seems to be treating Month([dteSurvey]) like an string and
yet as far as I can tell, there is nothing I'm doing that would turn
that number into a string.

I would greatly appreciate any hints anyone might have as to how to
get my data treated as the correct data type.

Thanks!
Alice

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

 

[night_writer]

Jerry:

Thank you for your response. I tried the Val() function suggested by
J_Goddard, and it did the trick, but I appreciate your assistance.
Thank you!

Alice

1. Show use the SQL for the query.

2. 12/1/2010 -- Is that DD/MM/YYYY or MM/DD/YYYY?

3. "The field is formatted as a date."  Does this mean that the field is a
Date/Time data type in the table OR it's a text field that looks like a date?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I hope someone can help me find the reason for this behavior.

I am using a query to run a report that needs to capture status at the
end of the month, so I need to exclude records that might have been
added later than the month end I'm interested in.

I do this by setting a month and year on a form named Splash. The
month field, [cbxMonth], is a combo box with column 1 being MonthID as
an integer and column 2 being the month name as a string. The bound
column is column 1. (I started this as a value list, and then tried
basing it on a table just in case there was something wrong with the
way I defined this field. Neither method solved my problem.)

My query selects records from a table based on a date in the record
[dteSurvey]. The field is formatted as a date. My query field is an
expression: Month: Month([dteSurvey]) with the criteria: <=[Forms]!
[Splash]![cbxMonth].

This is what's driving me nuts. I have an entry in the data base of
12/1/2010. When I set [cbxMonth] to any month except January, the
December entry is included in the results. If I set [cbxMonth] to
January, the December results are excluded. If I remove the criteria
formula and just put a number in, like "4" (without quotes), the
December results are excluded as they should be.

My query seems to be treating Month([dteSurvey]) like an string and
yet as far as I can tell, there is nothing I'm doing that would turn
that number into a string.

I would greatly appreciate any hints anyone might have as to how to
get my data treated as the correct data type.

Thanks!
Alice
.- Hide quoted text -

- Show quoted text -

 

[KARL DEWEY]

I think a better way would be like this --

My_Calculated_Date: Format([dteSurvey], "yyyymm")

with the criteria: <=Format(CVDate([Forms]![Splash]![cbxMonth]),"yyyymm")

and enter date format in the form in a manner that Access will recognize
as a date.