Cannot pass date from form to query

[Tskutnik]

I have a form created to set parameters for a query.

A few of the fields have the standard method, using...
Forms![FormEntry]![FieldName]
in the Query criteria, and they are working correctly.

Three fields in the Query have the Fields defined by a value entered in the Form, using...
FieldName: Forms![FormEntry]![FieldName]
In the Query Field. Two of them are working correctly, and one is not.

The problematic field is a combo box that works correctly when it contains and passes text, but when dates are used nothing is passed.

Is there some special format I need to use in the Forms![FormEntry]![FieldName] string to pass a date field format to a query?

 

[AlexHedley]

You need to wrap dates in # date # in Access

 

[Tskutnik]

I'm not completely clear where that would go. Did you mean somewhere in the form and/or the query?

In the Query Field I tried:

Field: #[forms]![BulkErrorUpdates_Security]![dateselection]#, which does not seem to work,

or is this something I have to enter in the combo box section on the form?

 

[AlexHedley]

Try "#" & [Forms]![Formname]![controlname] & "#"

You mention a combobox, what is its datasource? Does it have more than one column?
You might need to use .column(#) property where # is column number, so then it uses that value as the criteria

i.e. [Forms]![Formname]![controlname].column(#)

If you try a Textbox instead would you be typing 15/09/2012 into it?

 

[Tskutnik]

"#" & [Forms]![Formname]![controlname] & "#"
gave me
#12/31/2012#
as a result, as a concatenation.

The datasource is a query with a series of dates. It has only one column.

I tried changing the combobox contents to a text list and it worked fine, so it has something to do with the dates. Proof also is 12/31/2012 did not make it to the query result but #12/31/2012# did. Once it it not a date it works fine.

I've tried every combinaiton of setting and clearing the format fields in both the combo, coubo source, and the query field and no luck.

 

[AlexHedley]

I presume the field is set to date/time in the table/query you are searching in?

Is the list you are creating the combo from in a separate table with a field type of date/time too?

 

[Tskutnik]

The Combo is looking at a query with the date defined as short date (when I click any result in the date column I get the calendar popup opiton so it is definitely recognized as a date).

That query pulls from a table with the date field defined as date/time.

I checked everything all the way from the combo source through all the queries back to the tables and everything looks like a date.

 

[AlexHedley]

Could it be the way access treats dates and the <= issue because of the time portion of it?
Are you wanting them to match that exact day from the combo?

 

[Tskutnik]

I need them to pick form a date list, yes. Though about just a text box but it will not work.

Not sure what you mean about the <= issue. I just found that another similar passing dates from forms to queries process in the DB is working fine. Let me check into that for a minute.

The only thing unique thing between the working and not working functionality is the not working data comes thoruhg a union query earlier in the flow and the working one does not. Can't figure out why that would matter since the formats are dates with either route.

 

[Tskutnik]

Not completely sure I know the answer but it certainly seems like you cannot plug a date in a Field:[Forms]![Formname]![controlname]. This plug works fine with text but does not seem to cooperate with dates.

I tried all the combinations I could with "#" before and after and .value, with no luck.


I figured out a work around for now, but jeez....

If there are no more suggestions I'll close this thread.

 

[mahenkj2]

Have you specified format for dates at table level? If not, you might need to use format or CDate function in the query. In case, does not resolve, can you post a trimmed version of your dB.

 

[Tskutnik]

I rewrote the process to avoid the union query in the middle and it seemed to work.
Thanks for the help