Dates in a listbox- Data type mismatch in criteria expression

[David Ball]

Hi,
I have a listbox on a form populated by the following code behind a control button:

SELECT DISTINCT [tblSMSdata].[Reporting Week Ending] FROM tblSMSdata UNION SELECT "ALL" FROM tblSMSdata

The dates in the table are in the format dd-mmm-yyyy (25-Apr-2016) but in my Listbox the dates appear like 25/04/2016. When I try to run the code I get a message “Data type mismatch in criteria expression”. I am assuming this is because the dates are in different formats. How can I have the dates in the listbox the same as the dates in the table?
Also, the dates in the listbox are not sorted in chronological order. Am I able to have the dates in ascending order in my listbox?
Thanks very much
Dave

 

[pbaldy]

The data types in a UNION query must be compatible. Try this, which forces the date to text:

SELECT DISTINCT Format([tblSMSdata].[Reporting Week Ending], "yyyy/mm/dd") As WeekEndDate FROM tblSMSdata UNION SELECT "ALL" FROM tblSMSdata

 

[pbaldy]

Oh, and you can add an ORDER BY clause.

 

[David Ball]

Thanks very much, I will give it a try

 

[David Ball]

I used "dd-mmm-yyyy". The dates in my listbox now look exactly like those in the table (and the query based on the table), but I still get the data mismatch error. Not sure why?

 

[static]

The erroring code is probably expecting a date datatype, but using the format() function and adding "All" into the query forces all values to be converted to string.
You need to either remove the formatting and don't include string values or convert back to a date.

if mylistbox <> "ALL" then
    thedate = cdate(mylistbox)
end if

 

[pbaldy]

What code is throwing the error? I don't get one with the UNION query, so I was wrong about that. It's probably as static said, the date being converted to text is causing a problem. You can probably get the sorting by leaving the date field as a hidden field, and sorting on that.

 

[David Ball]

Thanks static, but I'm not sure where that code would go (It's getting way over my head, I can't believe what a saga it is to try to use dates in a listbox!).

Is there a way to modify what is in the Row Source of my listbox (see below) to make these dates instead of text (but still "dd-mmm-yyyy")?

SELECT DISTINCT Format([tblSMSdata].[Reporting Week Ending], "dd-mmm-yy") As WeekEndDate FROM tblSMSdata UNION SELECT "ALL" FROM tblSMSdata

Originally I just left them as dates and some of them worked and others didn't. If a date in my table and query was 07/05/2017 it would be 7/05/2017 (without the 0) in my listbox and I would get the error. I spend a whole day mucking around with different formats trying to get this to work.


Thanks very much

Dave

 

[static]

The rowsource for your list should be

SELECT DISTINCT [Reporting Week Ending] FROM tblSMSdata

If the dates in your table have date datatypes then they will be real dates in the list. It doesn't matter what they look like.

Run your code. Error gone? Good.

So why are you adding ALL to the list?
You usually add all to a combo to say "run the code for everything".
If you have a multi-select list box you are better to have a separate check box that selects/deselects all the list items.

 

[David Ball]

Thanks static, I will try that

 

[David Ball]

I finally got this working. I had to change the code that built my "In" expression for the criteria in the query by replacing the single quotes with # signs.

The criteria was In (‘7/04/2017’) and it needed to be In (#7/04/2017#) to work with dates.

Thanks

Dave

 

[David Ball]

One more complication! When I select 07-Apr-2017 in the listbox it puts In (#4/07/2017#) in the query criteria row. It thinks 7th April is the 4th July?

If I manually adjust that date in the criteria row to be 7/04/2017 it runs properly and returns the correct records.

Is there a way to fix this?

Thanks very much

Dave