More efficient use of LIKE in a query?

  • Thread starter Thread starter Rune Hellem
  • Start date Start date
R

Rune Hellem

Hi

My challenge is that I have a tabel with a column wich represents a date.
Because it's updated through a third-party tool once in a while I get this
problem. The text is in most of the cases formated like this MM/DD/YYYY, but
some rows is formated like this as well YYYY/MM/DD

When discovering this I made the following WHERE clause
......
AND datecolumn LIKE year + '/' + month + '/??'
OR datokolonne like month + '/??/' + year
......

where year and month is user input.

When not having the OR it works all fine, but when I put on OR it goes
crazy, and after a while I don't bother anymore and must shut down Access...

Is it possible to do this a _bit_ more efficient, or should I instead make a
macro which cleans up the format differences?

in advance, tnx

Rune
 
Rune,

I guess what you are trying to do is limit your query to those records
for a specific month and year, where the date field is a text datatype
rather than a date. You can use the CDATE() function to convert a
text string to a date.

Add two new columns (qryMonth and qryYear) to your query

qryMonth:MONTH(CDATE([yourfield]))
qryYear: YEAR(CDATE([yourfield]))

You can leave them in your output to make sure they are working
properly. Then add the month and year you are looking for into the
criteria lines of the query grid.

--
HTH

Dale Fye


Hi

My challenge is that I have a tabel with a column wich represents a
date.
Because it's updated through a third-party tool once in a while I get
this
problem. The text is in most of the cases formated like this
MM/DD/YYYY, but
some rows is formated like this as well YYYY/MM/DD

When discovering this I made the following WHERE clause
......
AND datecolumn LIKE year + '/' + month + '/??'
OR datokolonne like month + '/??/' + year
......

where year and month is user input.

When not having the OR it works all fine, but when I put on OR it goes
crazy, and after a while I don't bother anymore and must shut down
Access...

Is it possible to do this a _bit_ more efficient, or should I instead
make a
macro which cleans up the format differences?

in advance, tnx

Rune
 
# Dale wrote

| You can use the CDATE() function to convert a
| text string to a date.

Given a table with only two columns ID and DATE, with four rows

id date
1 2003/03/12
2 2003/03/10
3 03/12/2003
4 03/24/2003

The following SQL

SELECT * FROM Tabel1
WHERE MONTH(CDATE([date])) = month;

will return only row 1, 2 and 4 if month = 3. It misses row 3.

Since I can't pass "legal" formats to the cDate function,
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/ht
ml/vsfctcdate.asp
I guess the best solution might be to write a macro which formats all the
rows the same way...DD/MM/YYYY

Rune
 
Hi Rune,

PMFBI

*If* your Windows Regional Setting are set
to English(United States)
and Short Date Style = M/d/yyyy,

CDate(), Month(),Day(),Year() functions
are very forgiving of a date string.

Given your example table where
ADate is a string (otherwise, if it
was type Date/Time, they would
all be converted to the *same* Regional
Setting as they are entered.... right?)

ID ADate
1 2003/03/12
2 2003/03/10
3 3/12/2003
4 3/24/2003


SELECT
Tabel1.ADate,
Month([ADate]) AS TryMonth,
CDate([Adate]) AS TryCDate,
Month(CDate([ADate])) AS TryMonthCDate,
SQLDate([ADate]) AS TrySQLDate
FROM Tabel1;


ADate TryMonth TryCDate TryMonthCDate TrySQLDate
2003/03/12 3 3/12/2003 3 #2003/03/12#
2003/03/10 3 3/10/2003 3 #2003/03/10#
3/12/2003 3 3/12/2003 3 #2003/03/12#
3/24/2003 3 3/24/2003 3 #2003/03/24#

But from your "datokolonne," I assume you don't
have US Regional Settings...so CDate chokes on
trying to decide if the month of "3/12/2003" is 3 or
12, each valid guesses.

SQLDate() is a function often used to handle nonUS dates.

Public Function SQLDate(pDate As Variant) As String
If IsDate(pDate) Then
SQLDate = Format$(pDate, "\#yyyy\/mm\/dd\#")
Else
SQLDate = "#9999/9/9#"
End If
End Function

I believe you could adjust this function to provide a
consistent date string that you could use in your SQL
and apply your LIKE criteria to the result of the function.

Public Function SQLDateString(pDate As Variant) As String
If IsDate(pDate) Then
SQLDateString = Format$(pDate, "yyyy\/mm\/dd")
Else
SQLDateString = "9999/9/9"
End If
End Function
 
tnx Gary

I ended up making a VBA-function wich runs through the column and rearrange
the date-fields so they all have the same format...

Rune
 
Back
Top