Integers behaving like strings
in [Queries]
|
From: night_writer on 5 May 2010 14:04 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
From: J_Goddard via AccessMonster.com on 5 May 2010 14:47 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 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 -- John Goddard Ottawa, ON Canada jrgoddard at cyberus dot ca Message posted via http://www.accessmonster.com
From: Jerry Whittle on 5 May 2010 15:02 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. "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 > . >
From: night_writer on 5 May 2010 15:12 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 On May 5, 1:47 pm, "J_Goddard via AccessMonster.com" <u37558(a)uwe> wrote: > 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 > > > > > > 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 > > -- > John Goddard > Ottawa, ON Canada > jrgoddard at cyberus dot ca > > Message posted viahttp://www.accessmonster.com- Hide quoted text - > > - Show quoted text -
From: night_writer on 5 May 2010 15:14
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 On May 5, 2:02 pm, Jerry Whittle <JerryWhit...(a)discussions.microsoft.com> wrote: > 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. > > > > "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 > > .- Hide quoted text - > > - Show quoted text - |