Pass text date to query

Domski74

Registered User.
Local time
Today, 07:53
Joined
Jan 29, 2009
Messages
18
Hi All

I have a form that has 3 combo boxes for the user to enter a date, one each for day, month and year - so far so good.

The problem I'm having is trying to pass this date into a query, I have the query criteria as dateValue([forms]![cboDay]&"\"&[forms]![cboMonth]&"\"&[forms]![cboYear]) but this doesn't work, it says it's too complicated to be evaluated?

Please can someone point me in the right direction?

Thanks
 
Hi -

Try the DateValue() function to convert your string to true datetime data format. Here's an example from the debug (immediate) window:

Code:
x = "01/Mar/2009"
y =  [B]datevalue(x)[/B]
? y
3/1/2009

To show that it's actually in datetime data format: 
? cdbl(y)
 39873

HTH - Bob
 
Thanks for the reply.

Are you saying I should do the conversion in vba?

I was using the datevalue function to try to convert it in the criteria section of the query.

When I try this in vba it says type mismatch?

Code:
x = DateValue(Me.cboDay & "\" & Me.cboMonth & "\" & Me.cboYear)

Please elaborate. Thanks
 
Or you might try using DateSerial:

DateSerial([forms]![cboYear], [forms]![cboMonth], [forms]![cboDay])
 
Glad you got it working. In a query, using the DateValue() function, it'd be:

DateValue(Me.cboDay & "/" & Me.cboMonth & "/" & Me.cboYear) as MyDate,

Bob

ADDED:

The same thoughts came to mind just as I hit the Save button. Agree completely
with both slash vs. backslash and the form name issues.
 
Where are you actually doing this? I'm not sure about you reference to [forms]. If it'd being done in code behind the form, you probably should simply being using Me.cboDay. If you're actually doing this in a query, I think you need to actually supply the name of the form, i.e. [Forms]![FormNameHere].

What I do know is that DateValue() won't convert a string to a date value with back slashes (\) instead of forward slashes (/), as that's not a recognized date format, so you definitely need to correct that.
 
backslashes! What a moron I am.

It's been a long week!

Thanks for all your help.

Domski.
 

Users who are viewing this thread

Back
Top Bottom