Integers behaving like strings
in [Queries]
|
From: KARL DEWEY on 5 May 2010 15:26 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. -- Build a little, test a little. "night_writer" wrote: > 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 > . > |