Querying a text column that contains either text or date 1

[monkeymagic2222]

Hi,

I know there are loads of entries on here about converting text fields to date fields in access sql. My problem is I have a text column that may contain either a date or a text string but I only want to query dates. e.g. the column issued_date could contain: 01/02/2009 or "Date tba"

Is there a way to query the text field for dates and ignore any with text in (avoiding the data type mismatch error).

My sql is:
SELECT capex.ID, capex.capex_no, capex.issued_date, capex.description, capex.value
FROM capex
WHERE FORMAT(CDATE(issued_date),'dd/mm/yyyy') >= #01/01/2009#;

Thanks.

 

[Remou]

How about:

SELECT capex.ID, 
       capex.capex_no, 
       capex.issued_date, 
       capex.description, 
       capex.value
FROM   capex
WHERE  Format([issued_date],"mm/dd/yyyy")>=#01/01/2009# 
AND    IsDate([issued_date])=True

http://lessthandot.com

 

[MajP]

That is such a bad table design, you may have people reluctant to try to put a band-aid on the problem. The reason is this will snowball to more problems that need band-aids. If you store dates as Dates you will be able to do a lot more. You are violating database normalization by having two "data types" in the same field.
The better solution would be to fix the database structure. This does not mean you will lose your data, you can run some queries and build some functions to move the formatted strings that look like xx/xx/xxxx into a new real date field and leave everything else in the text field. Are you amenable to that?

What is this field?

 

[monkeymagic2222]

Hi guys, thanks for the feedback.

Remou - no i'm afraid I still get the data mismatch error with that.

MajP - Yeah I know it's not ideal, it's a very old asp and access solution that I will be rewriting with .net and sql eventually but for now I need a quick fix. I've no problem adding functions and extra fields as long as the original data stays the same.

The field contains the date capital expenditure was issued to a project but sometimes they may not have a date and so write some text in there instead. I wrote it about 8 years ago and didn't really know what I was doing then, hence the poor structure.

Cheers,

Simon

 

[PHV]

SELECT ID, capex_no, issued_date, description, value
FROM (
SELECT ID, capex_no, issued_date, description, value
FROM capex WHERE IsDate(issued_date)
) AS D
WHERE CDate(issued_date) >= #2009-01-01#;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

I still would make a query to seperate the data, then you would have a lot more flexibility of writing queries.

original data

txtDate
19 Mar 2009
1/1/2008
10/11/1992
other
May 1 1882
No entry
January 17 1911
No entry Made

sql

SELECT tblPatientInfo.txtDate, IIf(IsDate([txtDate]),CDate([txtDate]),Null) AS DateVals, IIf(IsDate([txtDate]),Null,[txtDate]) AS TxtValues
FROM tblPatientInfo;

results

txtDate	           DateVals	TxtValues

1/1/2008         1/1/2008	
10/11/1992       10/11/1992	
19 Mar 2009      3/19/2009	
January 17 1911  1/17/1911	
May 1 1882       5/1/1882	
No entry                       No entry
No entry Made                  No entry Made
other                          other

now you have a lot more flexibility of sorting data and running other queries on the data. And if feasible run a make table query to normalize the data structure.