VBA, VBA COntrolled Date Range in SQL Query

VBA COntrolled Date Range in SQL Query

Author	Message
Paul Amble #1 / 6	VBA COntrolled Date Range in SQL Query Hello, I am having a really hard time trying to get the following line in my SQL query to run: swhere = "(((epinspect.datein) >= [Forms]! [specificlogdata]![Text41]) and ((epinspect.datein) <= [Forms]![specificlogdata]![Text42]))" I want to generate a query of the dates between and including the values in fields text41 and text42. The system will only allow for values between text41 and text42 AND equal to text42 (as long as text42 does not equal text41)! It will not return any records equal to text41, nor will it return any values when text41 is equal to text42. What am I doing wrong? I got the >=/<= right off of the MS knowledgebase... Help!?! Paul Ambler
Sat, 10 Dec 2005 05:02:57 GMT

Andrew Smit #2 / 6	VBA COntrolled Date Range in SQL Query I'm rather surprised your code gets you any results at all other than prompts for the values of [Forms]![specificlogdata]![Text42] and [Forms]![specificlogdata]![Text41]. You need to pass the dates as strings enclosed in # marks, and formatted in US date format of mm/dd/yyyy. If you are in the US then the formatting may not be an issue, but everywhere else it is! Try rewriting the code as follows: swhere = "datein >= #" & [Forms]![specificlogdata]![Text41] & "# and datein <= #" & [Forms]![specificlogdata]![Text42] & "#" (I've removed the brackets and table names as these are not needed unless you have another field with the same name from a different table). I've ignored the format issue above. You need to use the format command to ensure the dates are correctly formatted if there is any chance they may not be in the right format. Quote: > Hello, > I am having a really hard time trying to get the > following line in my SQL query to run: > swhere = "(((epinspect.datein) >= [Forms]! > [specificlogdata]![Text41]) and ((epinspect.datein) <= > [Forms]![specificlogdata]![Text42]))" > I want to generate a query of the dates between and > including the values in fields text41 and text42. The > system will only allow for values between text41 and > text42 AND equal to text42 (as long as text42 does not > equal text41)! It will not return any records equal to > text41, nor will it return any values when text41 is > equal to text42. > What am I doing wrong? I got the >=/<= right off of the > MS knowledgebase... > Help!?! > Paul Ambler
Sat, 10 Dec 2005 05:25:49 GMT

Dirk Goldga #3 / 6	VBA COntrolled Date Range in SQL Query Quote: > Hello, > I am having a really hard time trying to get the > following line in my SQL query to run: > swhere = "(((epinspect.datein) >= [Forms]! > [specificlogdata]![Text41]) and ((epinspect.datein) <= > [Forms]![specificlogdata]![Text42]))" > I want to generate a query of the dates between and > including the values in fields text41 and text42. The > system will only allow for values between text41 and > text42 AND equal to text42 (as long as text42 does not > equal text41)! It will not return any records equal to > text41, nor will it return any values when text41 is > equal to text42. > What am I doing wrong? I got the >=/<= right off of the > MS knowledgebase... I don't see anything immediately wrong, and something very similar works fine for me. It may have to do with the interpretation of the text boxes as dates. Do those text boxes have a Format property applied? If so, what is it? Are they unbound, or are they bound to date/time fields? Here's another thing to check: what values are actually in the field [epinspect.datein]? If they were set from the function Date(), they'll contain only dates, without any time portion, and all will be well. If they were set from the function Now(), they'll have both date and time portions, and will not as a rule be equal to a date with no time. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
Sat, 10 Dec 2005 05:49:27 GMT

Paul Amble #4 / 6	VBA COntrolled Date Range in SQL Query I am using the Calendar control (activeX) date picker to select my dates. I know that this passes the date and time to my text box. I have a format on these text boxes that is short date. Do I need more of a format control than this? Quote: >-----Original Message----- >> Hello, >> I am having a really hard time trying to get the >> following line in my SQL query to run: >> swhere = "(((epinspect.datein) >= [Forms]! >> [specificlogdata]![Text41]) and ((epinspect.datein) <= >> [Forms]![specificlogdata]![Text42]))" >> I want to generate a query of the dates between and >> including the values in fields text41 and text42. The >> system will only allow for values between text41 and >> text42 AND equal to text42 (as long as text42 does not >> equal text41)! It will not return any records equal to >> text41, nor will it return any values when text41 is >> equal to text42. >> What am I doing wrong? I got the >=/<= right off of the >> MS knowledgebase... >I don't see anything immediately wrong, and something very similar works >fine for me. It may have to do with the interpretation of the text >boxes as dates. Do those text boxes have a Format property applied? If - Hide quoted text - - Show quoted text - Quote: >so, what is it? Are they unbound, or are they bound to date/time >fields? >Here's another thing to check: what values are actually in the field >[epinspect.datein]? If they were set from the function Date(), they'll >contain only dates, without any time portion, and all will be well. If >they were set from the function Now(), they'll have both date and time >portions, and will not as a rule be equal to a date with no time. >-- >Dirk Goldgar, MS Access MVP >www.datagnostics.com >(please reply to the newsgroup) >.
Sat, 10 Dec 2005 11:16:40 GMT

Dirk Goldga #5 / 6	VBA COntrolled Date Range in SQL Query Quote: > I am using the Calendar control (activeX) date picker to > select my dates. I know that this passes the date and time > to my text box. I have a format on these text boxes that > is short date. Do I need more of a format control than > this? I don't know whether the Calendar control passes a non-zero time or not; I haven't used the control, and it doesn't really make sense to me that a calendar control would pass a time as well as a date. If it does, though, that could be your problem. The format of the text boxes controls how they display their contents, but not what the contents actually are -- if you have a time value in there with the date, that's what the query will be using. With the form open, try entering these lines (one at a time, and press enter after each one) in the Immediate window of the VB Editor environment to see what's really in those text boxes: ?Forms!specificlogdata!Text41 ?Forms!specificlogdata!Text42 That'll tell us whether we're barking up the wrong tree here. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
Sat, 10 Dec 2005 11:34:34 GMT

Dirk Goldga #6 / 6	VBA COntrolled Date Range in SQL Query Quote: >> I am using the Calendar control (activeX) date picker to >> select my dates. I know that this passes the date and time >> to my text box. I have a format on these text boxes that >> is short date. Do I need more of a format control than >> this? > I don't know whether the Calendar control passes a non-zero time or > not; I haven't used the control, and it doesn't really make sense to > me that a calendar control would pass a time as well as a date. If > it does, though, that could be your problem. The format of the text > boxes controls how they display their contents, but not what the > contents actually are -- if you have a time value in there with the > date, that's what the query will be using. > With the form open, try entering these lines (one at a time, and press > enter after each one) in the Immediate window of the VB Editor > environment to see what's really in those text boxes: > ?Forms!specificlogdata!Text41 > ?Forms!specificlogdata!Text42 > That'll tell us whether we're barking up the wrong tree here. Actually, come to think of it, it's not just the text boxes on the form that have to be checked; you also should look at what's actually stored in the [datein] field in the table. Does that field contain date-only values or date+time values? Again, it's not the format of the field, it's how you assign values to it that counts. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)
Sat, 10 Dec 2005 11:58:30 GMT

Page 1 of 1

[ 6 post ]