help with Search form

BAzZ

Registered User.
Local time
Today, 15:04
Joined
Nov 13, 2002
Messages
60
I have created a Search form using the code at this URl :
http://support.microsoft.com/default.aspx?scid=kb;EN-US;210242

it works except I cant use it to search by date, which I need to do.
It will only search in U.S. format, MM/DD/YYYY, as I am in Australia I need to search in the following format DD/MM/YYYY.

I have checked the local machine settings and the short date is in the right format, it just seems to be either Access or the code.

Could someone please help me with this issue?
 
Date: "Select * from [Orders] where [OrderDate] = #" & Format([Mycriteria],mm/dd/yyyy) & "# ;"
 
This is the code I have:

Private Sub cmdRunSearch_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null
If Left(Me![First Name], 1) = "*" Or Right(Me![First Name], 1) = "*" Then
where = where & " AND [First Name] Like '" + Me![First Name] + "'"
Else
where = where & " AND [First Name]='" + Me![First Name] + "'"
End If

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

where = where & " AND [Permit Number]= '" + Me![Permit Number] + "'"

where = where & " AND [Date of Application]= #" + Me![Date of Application] + "# "

Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from [1 Application Details] " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub

This is the line of code I am having problems with the date:
where = where & " AND [Date of Application]= #" + Me![Date of Application] + "# "
from this code how would I manipulate it to display the date as dd/mm/yyyy, I tried using the code by Rich but keep getting an overflow error.

Thanks in advance
 
Please, someone I need this code, or for someone to steer me in the right direction urgently

Thanks
 
Dates are treated as strings so use what Rich has told you.
 
but I keep geetin an overflow error message, so I'm not sure how to modify the code I have tried changing the code like this:
where = where & "Select * from [1 Application Details] where [Date of Application] = #" & Format([Date of Application], mm / dd / yyyy) & "# ;"

but I keep getting a "Run-time error '6': overflow message
 
As you are concatenating your "Where" clause, you do not want the SELECT in your date line.

Try:

where = where & " AND [Date of Application]= #" & Format([Date of Application],mm/dd/yyyy) & "#"

HTH

Brad.
 
Thanks Brad,

But I am still getting:"Run-time error '6': overflow message

when I Debug it is on that line of code.
where = where & " AND [Date of Application]= #" & Format([Date of Application], mm / dd / yyyy) & "#"
 
Sorry Bazz,

Didn't pick up the typo in earlier post.

Replace the line with:

where = where & " AND [Date of Application]= #" & Format([Date Of Application], "mm/dd/yyyy") & "# "


*Note the addition of " " around the mm/dd/yyyy in the format statement.

Cheers

Brad
 
You are an absolute God Send! Thank you soo much for that.

Cheers
 
sorry to be a pain, but now when I try to search with out the date field been filled in I come up with Run-time error '3075'.

If I put a date in it works fine.
 
Bazz,

This is due to your expression concatenating the "# & Null & #" resulting in:
where = ##

You can handle this by using an expression such as:

If Not IsNull(Me![Date of Application]) Then . . . (your where statement line)

That way, if the date is left blank (null) then it will not appear in your Where clause.

HTH

Brad.
 
Bazz,

As a problem solving tool, you can add msgbox lines to inform you of how the where clause is 'developing'

If you add:
Msgbox where

before your Set QD = . . . line, you will have the where clause presented in a message box. This way you can see how access is interpreting you Where Clause. In this case, you would have seen the ## concatination.

Once you have corrected the issue, you can comment out the msgbox line(s).

Cheers

Brad.
 
Brad,

Seriously, thanks for everything.

I actually had a message box, and it showed me # #, but I didn't know how to solve the issue.

Thanks again

Cheers
 

Users who are viewing this thread

Back
Top Bottom