asp Access Date query in wrong format

GegH
GegH used Ask the Experts™
on
I am creating a search page to search for entries between 2 dates already entered in the database.
The date in access is UK format (dd/mm/yyyy) and i am using 2 drop menus to pick the dates from which show correctly.
When i submit the form there are no records.
I tested the query and it works if i search using the US format (mm/dd/yyyy).
How can i get the drop down menus to post the data as US Format rather than UK so it will pick up the records from Access?
<select name="StartDate" id="select2">
                <%
While (NOT rsDate.EOF)
%>
                <option value="<%=(rsDate.Fields.Item("tWeekBegin").Value)%>"><%=(rsDate.Fields.Item("tWeekBegin").Value)%></option>
                <%
  rsDate.MoveNext()
Wend
If (rsDate.CursorType > 0) Then
  rsDate.MoveFirst
Else
  rsDate.Requery
End If
%>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Year 2008
Top Expert 2008
Commented:
instead of doing:
SELECT tWeekBegin FROM ...

DO the following:
SELECT tWeekBegin, FORMAT([ tWeekBegin],'mm/dd/yyyy') AS optValue FROM ...

THEN in your asp change:
                <option value="<%=(rsDate.Fields.Item("tWeekBegin").Value)%>"><%=(rsDate.Fields.Item("tWeekBegin").Value)%></option>


to:
                <option value="<%=(rsDate.Fields.Item("optValue").Value)%>"><%=(rsDate.Fields.Item("tWeekBegin").Value)%></option>

Author

Commented:
Thankyou so much..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial