help with Search form

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 2, 2003

• #1

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?

 

R

Rich

Guest

• Apr 2, 2003

• #2

Date: "Select * from [Orders] where [OrderDate] = #" & Format([Mycriteria],mm/dd/yyyy) & "# ;"

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 3, 2003

• #3

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

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 6, 2003

• #4

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

Thanks

 

Mile-O

Back once again...

Local time

Today, 06:03

Joined

Dec 10, 2002

Messages

11,316

• Apr 7, 2003

• #5

Dates are treated as strings so use what Rich has told you.

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 7, 2003

• #6

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

 

bradcccs

Registered User.

Local time

Today, 15:03

Joined

Aug 9, 2001

Messages

461

• Apr 7, 2003

• #7

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.

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 7, 2003

• #8

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) & "#"

 

bradcccs

Registered User.

Local time

Today, 15:03

Joined

Aug 9, 2001

Messages

461

• Apr 7, 2003

• #9

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

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 7, 2003

• #10

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

Cheers

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 7, 2003

• #11

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.

 

bradcccs

Registered User.

Local time

Today, 15:03

Joined

Aug 9, 2001

Messages

461

• Apr 7, 2003

• #12

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.

 

bradcccs

Registered User.

Local time

Today, 15:03

Joined

Aug 9, 2001

Messages

461

• Apr 7, 2003

• #13

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.

 

B

BAzZ

Registered User.

Local time

Today, 14:33

Joined

Nov 13, 2002

Messages

60

• Apr 8, 2003

• #14

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