Treating Dates With Wildcards

[Sharky II]

How can i modify this date field code to accept wildcards?

Hi guys.

I'm using the query by form method, and am searching for dates. Here's the code i have at the moment which searches for exact dates only (this works fine):

If Not IsNull(Me![SrchDateOfDeparture]) Then
where = where & " AND [DateOfDeparture]= #" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "#"
End If

SrchDatefDeparture is the name of the unbound field in the search form. DateOfDeparture is the actual field name in the query/table. As you can see i've formatted it so that i don't get problems with UK/US formats (when searching for 12/04/2004 etc). I need to modify the code above so that the user can search for */12/2003 (all trips in dec 2003) or */12/* (all trips in dec) or whatever. I don't have a clue how to do this. I understand you can do stuff like:

select * from table where month( [datefield] ) = 01
select * from table where year( [datefield] ) = 1982
select * from table where day( [datefield] ) = 27

etc but i don't know how that helps me.

Anyone got any ideas? Many thanks in advance

 

[namliam]

try using format$(Datefield,"dd/mm/yyyy")

you can then do: where format(dateField,"dd/mm/yyyy") like "*/12/*" without any problem since the format turns the date into a string.

GL

Regards

 

[Sharky II]

try using format$(Datefield,"dd/mm/yyyy")

you can then do: where format(dateField,"dd/mm/yyyy") like "*/12/*" without any problem since the format turns the date into a string.

GL

Regards

sorry i don't understand... are you saying if i simply add a dollar sign in there i can use wildcards? I tried that and it didn't work... i am already doing what you suggested man (see my original post).... am i beign amazingly dim again?

Cheers

 

[namliam]

SELECT myTable.dateTime
FROM myTable
WHERE Format([dateTime],"dd/mm/yyyy") Like "*/11/2003"

I mean something like above.

The format changes the date into a string and you can then do anthing to it as you would a normal string (eg wildcards)

Clear enough ?

Regards

 

[Sharky II]

but that's what i've already got (modified to work with query by form method)? and it doesn't work? like i say my code is:

If Not IsNull(Me![SrchDateOfDeparture]) Then
where = where & " AND [DateOfDeparture]= #" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "#"
End If

which is the same as yours? i've used the format function which does turn it to a string. but in the form when i enter a date like *04/2004 the query is created:

Select * from qryCompleteSearch3 WHERE [DateOfDeparture] = #*/04/2004#;

which is fine. but then i get the runtime error 3075: syntax error in date in query expression 'DateOfDeparture] = #*/04/2004#'.

What am i doing wrong? Am i being a shmuck? where do i use the 'like' part?

 

[namliam]

which is the same as yours?

No its not look closely. If it still looks the same. GO HOME, go to sleep and look at it again tomorrow. If then still get glasses, if then yet still.... well just making fun offcourse.

Look at the location of the format its different. You will have to do 2 different fields/thing/querys for exact dates and wildcards dates.

Regards

 

[Sharky II]

bah, maybe i should go home.

i managed to get a wildcard working on a normal text field:

If Left(Me![SrchTripDestination], 1) = "*" Or Right(Me![SrchTripDestination], 1) = "*" Then
where = where & " AND [TripDestination] like '" + Me![SrchTripDestination] + "'"
Else
where = where & " AND [TripDestination]= '" + Me![SrchTripDestination] + "'"
End If

but i can't repeat the success with the date field. the code i have now (which is probably gobbledygook) is:

If Not IsNull(Me![SrchDateOfDeparture]) And Left(Me![SrchDateOfDeparture], 1) = "*" Or Right(Me![SrchDateOfDeparture], 1) = "*" Then
where = where & " AND [DateOfDeparture] like #" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "#"
End If

I dunno where to move my 'format' to? The above gives me the query Select * from qryCompleteSearch3 WHERE [DateOfDeparture] like #*/04/2004#; and then a run time error. Getting rid of the left and right part gives the same error anyway.

Sorry for getting on your nerves mailman

 

[namliam]

I am maybe THE most pacient person there is.....was just kidding

Select * from qryCompleteSearch3 WHERE format([DateOfDeparture],"dd/mm/yyyy") like "*/04/2004"

NOTE that you are now working with strings not dates thus NO # but "

and try something like:
if not isnull() then
If instr(Me![SrchDateOfDeparture],"*") >0 then
.... put your like thing here....
else
.... put your "= # #" thing here ....
endif
endif

Regards

 

[namliam]

Also on the strings and the like....

Try doing: like 'Something' (yes without any wildcards)

I think you will find it works.....

Regards again

 

[Sharky II]

yesssss!

thanks mailman! what i had to do was use single quotes - i tried using double quotes and hash (as above) but if it's a string then surely u need single quotes? anyway it works code is simple:

'*************************************
'SrchDateOfDeparture Date Field Code *
'*************************************

    If Not IsNull(Me![SrchDateOfDeparture]) Then
        where = where & " AND [DateOfDeparture] like '" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "'"
    End If

Thanks for your patience!!!!!!!!

Disco time

 

[namliam]

Above was not what I meant. Also i cannot imagine it working. It appereantly does. I will have to test it .....

You have

where = where & " AND [DateOfDeparture] like '" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "'"

while i have
where = where & " AND format([DateOfDeparture], "mm/dd/yyyy") like '" & Me![SrchDateOfDeparture] & "'"

do you now see?

Regards

 

[Sharky II]

yeah man i was trying that before, but i get compile errors when i do that? the way i did it seems to work ok?

have a quick test of it

remember that SrchDateOfDeparture is the unbound field in the search form (this is a query by form method) and DateOfDeparture is the actual table/query field.

Best wishes

 

[Sharky II]

Problem: works with wildcards but not with FULL dates. To work with full dates (ie you know the exact date you want) you have to use the # delimeter.

Is there any way around having to have seperate fields for 'exact' date and for one with a wildcard in it? i am quite keen not to have double the amount of fields in the search form - there are already loads, and having a seperate field will confuse novice users.

i noticed that the example i gave before, with the SrchTripDestination works a treat - both with full desintation entered, and when a wildcard is used and when it's not also. I assume it's to do with the left/right parts of it. Can onyone tell me how to make this do the same but for dates?

That would be a tasty treat.

Cheers guys

Edd

 

[Sharky II]

I've got this code:

'*************************************
'SrchDateOfDeparture Date Field Code *
'*************************************
If Left(Me![SrchDateOfDeparture], 1) = "*" Or Right(Me![SrchDateOfDeparture], 1) = "*" Then
where = where & " AND [DateOfDeparture] like '" + Me![SrchDateOfDeparture] + "'"
Else
If Not IsNull(Me![SrchDateOfDeparture]) Then
where = where & " AND [DateOfDeparture] like #" & Format(Me![SrchDateOfDeparture], "mm/dd/yyyy") & "#"
End If
End If

Which works for 12/04/2004, it works for */04/2004, and it works for 12/04/* - but it does NOT work for 12/*/2004 - how do i do a 'in the middle' clause?

I am guessing this is hard/impossible?

Cheers

 

[Sharky II]

I am maybe THE most pacient person there is.....was just kidding

Select * from qryCompleteSearch3 WHERE format([DateOfDeparture],"dd/mm/yyyy") like "*/04/2004"

NOTE that you are now working with strings not dates thus NO # but "

and try something like:
if not isnull() then
If instr(Me![SrchDateOfDeparture],"*") >0 then
.... put your like thing here....
else
.... put your "= # #" thing here ....
endif
endif

Regards

instead of learning access, perhaps i should learn to read

thanks again man, you're helping me when you're not even here - magic!

 

[namliam]

Just 1 thing tho.... concatinate with the & not the + "Good practice"

Happy to help, as specially when i am not here.....

Regards