Using a Form to set a specific date in multiple queries Access 97

[cbenn]

I am having a problem with Microsoft Access 97. I want to set up a form which passes the date and time to multiple queries. for instance,

The below is my where statement.
Where

[Time] Between Forms!Time!BegTime and Forms!Time!EndTime

Now I am aware of how to set the sql statement up.
It should be in the following format
Where

.[time] between #2/4/01 7:00:00 AM# and #2/4/01 7:59:00 AM#

that works. However when the user inputs the above time in the form I get the correct data output however it does not produce any results. I have tried several other methods such as using the trim() function and I still get the same 0 records. However if I type the date in the query as stated above I get 6 rows.

Is there a problem with how I am setting this up? Most of the ways I have set this up Access complains of the query being to complex and that I should try to break the query up into variables.

I have also attempted to format it in the sql string attempting the following(this also complains of the above problem of being too complex.

where

.[time] between "#" & Forms!Time!BegTime & "#" and "#" & Forms!Time!EndTime & "#"
this also complains of the sql statement being too complex. However if I attempt the following:

where

.[time] between "#" & Forms!Time!BegTime & "#" and #2/4/01 7:59:00 AM#
the query works.


PLEASE any help is appreciaed!!

 

[RickSpr]

Use TimeValue(Forms!Time!BegTime) and TimeValue(Forms!Time!EndTime) in your WHERE clause.

Reason: The values coming from your text boxes are of type String. When you use "#" & to concatenate the pound signs, you're just creating a string with pound signs in it, not a date/time value. I think rather than convert the string to a date/time value, Jet is converting the date/time value to a string such as "12:30 PM". This then doesn't compare in a meaningful way to "#12:30 PM#".

TimeValue, on the other hand, returns a date/time value, which Jet can then directly compare. Rick Sprague

 

[cbenn]

I attemped this as suggested and it also did not return any rows. However I did notice that it returns only the 7:00:00 AM portion. Is there a function that returns the entire General Date? Like in the format above. 2/4/01 7:00:00 AM?

thanks

 

[RickSpr]

I was wondering if you really only had the time in those fields--guess I should have asked. Yes, the function you want is CDate(). Rick Sprague