By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Date search problem

P: n/a
Hi
I have a excel worksheet that contains a lot of data one on the columns
is the date(formatted as short date "dd/mm/yyyy)

i import this worksheet into access as a table the date field is
formatted as short date
problem is i cant query the dates, when i do it returns all the
records.
can anyone tell me how to query these dates correctly please

thanks
kevin

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
When you imported the Excel data, what kind of field did you end up with?
Open your Access table in design view, and check the Data Type of the field.

If you have a Date/Time field, you should be able to query it successfully.
For specifics, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If you ended up with some other kind of field, you will need to convert to a
date in order to get the right results, as string comparisons will be
unreliable. DateSerial(), Left(), Mid(), Right(), and CVDate() might help.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"kevcar40" <ke******@btinternet.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

I have a excel worksheet that contains a lot of data one on the columns
is the date(formatted as short date "dd/mm/yyyy)

i import this worksheet into access as a table the date field is
formatted as short date
problem is i cant query the dates, when i do it returns all the
records.
can anyone tell me how to query these dates correctly please

Nov 13 '05 #2

P: n/a

Allen Browne wrote:
If you have a Date/Time field, you should be able to query it successfully.
For specifics, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html


Canada is a member of the Commonwealth of Nations. Many Canadians use
mm/dd/yy. Few use dd/mm/yy.
Because Canada has adopted SI, the International System of Units, ISO
8601, is the basis for its "official" representation of Dates and Times
(Canada Standards Council has its own $35.00 manifestation of this).
ISO 8601 presents YYYY-MM-DD as the appropriate simple representation
of dates. YYYY-MM-DD has two strengths; it is easily sorted and it is
not so easily confused.
To the best of my knowledge, Access, VBA, JET, JET SQL and MS-SQL
always interpret YYYY-MM-DD correctly.
I recommend to everyone that he/she use YYYY-MM-DD everywhere.

Nov 13 '05 #3

P: n/a
Bear in mind that the Jet Sql does not interpret dates as dd/mm/yy.
AFAIK (open to correction) the dates should be in American Date Format
mm/dd/yy.
Alternatively, you'd require a function to change dd/mm/yy format into
mm/dd/yy format.

osmethod

Nov 13 '05 #4

P: n/a
Try this code in a modern version of Access:

CurrentProject.Connection.Execute "CREATE PROCEDURE QueryDate AS SELECT
* from Transactions WHERE fldDate < #2002-06-01#"
Debug.Print DBEngine(0)(0).QueryDefs("Querydate").SQL
End Sub

Nov 13 '05 #5

P: n/a
Thanks, Lyle.

I agree that the ISO standard yyyy-mm-dd is a) a standard, and b) the most
logical date format. IME also, JET always interprets it correctlly, so I am
not aware of any problems with using that approach.

However, I don't believe that is the standard Access/JET format for literal
dates. Just as VBA spins the date around to mm/dd/yyyy format, so any
literal date you type into the query interface in Access is converted to
mm/dd/yyyy format in the SQL statement.

It is therefore my recommendation that users and developers format literal
dates in SQL statements in the same way that Microsoft does.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lylefair" <ly***********@aim.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Allen Browne wrote:
If you have a Date/Time field, you should be able to query it
successfully.
For specifics, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html


Canada is a member of the Commonwealth of Nations. Many Canadians use
mm/dd/yy. Few use dd/mm/yy.
Because Canada has adopted SI, the International System of Units, ISO
8601, is the basis for its "official" representation of Dates and Times
(Canada Standards Council has its own $35.00 manifestation of this).
ISO 8601 presents YYYY-MM-DD as the appropriate simple representation
of dates. YYYY-MM-DD has two strengths; it is easily sorted and it is
not so easily confused.
To the best of my knowledge, Access, VBA, JET, JET SQL and MS-SQL
always interpret YYYY-MM-DD correctly.
I recommend to everyone that he/she use YYYY-MM-DD everywhere.

Nov 14 '05 #6

P: n/a
thanks all
i will try your suggestions and get back if any further problems
thanks again

Nov 14 '05 #7

P: n/a
Ok tried a few things but getting nowhere
my date field is formatted as short date(in both access and excel)
here is the code
i have 2 text boxes called txtStartDate and txtEndDate
these boxes hold the result of dates selected from a pop upcalender
this code returns all the dates
strSQL = "SELECT
All_Areas_Result.Identified_Repair,All_Areas_Resul t.Area,sum(All_Areas_Result.Quantity)
as Quantity " & _
"FROM All_Areas_Result " & _
"WHERE All_Areas_Result.Dates Between" &
SQLDate(Me.txtStartDate.Value) & "and " & SQLDate(Me.txtEndDate.Value)
& _
"GROUP BY All_Areas_Result.Identified_Repair,
All_Areas_Result.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Result_dated", acViewNormal

i have also tried
this code crashes

strSQL = "SELECT All_Areas_Result.Identified_Repair,
All_Areas_Result.Area, sum(All_Areas_Result.Quantity) as Quantity " & _
"FROM All_Areas_Result " & _
"WHERE All_Areas_Result.Dates Between # &
Me.txtStartDate.Value # and # Me.txtEndDate.Value # & _
"and [Area] <> 'Head'" & _
"and [Area] <> 'Hot Test'" & _
"GROUP BY All_Areas_Result.Identified_Repair,
All_Areas_Result.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Result_dated", acViewNormal
any suggestions?
thanks

kevin

Nov 14 '05 #8

P: n/a
If you open table All_Areas_Result in design view, what data type is your
Dates field? (Sometimes after import you end up with another type such as
Text.)

What result are you getting? An error message? A query with no records?
Access shut down by Windows? other?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"kevcar40" <ke******@btinternet.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Ok tried a few things but getting nowhere
my date field is formatted as short date(in both access and excel)
here is the code
i have 2 text boxes called txtStartDate and txtEndDate
these boxes hold the result of dates selected from a pop upcalender
this code returns all the dates
strSQL = "SELECT
All_Areas_Result.Identified_Repair,All_Areas_Resul t.Area,sum(All_Areas_Result.Quantity)
as Quantity " & _
"FROM All_Areas_Result " & _
"WHERE All_Areas_Result.Dates Between" &
SQLDate(Me.txtStartDate.Value) & "and " & SQLDate(Me.txtEndDate.Value)
& _
"GROUP BY All_Areas_Result.Identified_Repair,
All_Areas_Result.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Result_dated", acViewNormal

i have also tried
this code crashes

strSQL = "SELECT All_Areas_Result.Identified_Repair,
All_Areas_Result.Area, sum(All_Areas_Result.Quantity) as Quantity " & _
"FROM All_Areas_Result " & _
"WHERE All_Areas_Result.Dates Between # &
Me.txtStartDate.Value # and # Me.txtEndDate.Value # & _
"and [Area] <> 'Head'" & _
"and [Area] <> 'Hot Test'" & _
"GROUP BY All_Areas_Result.Identified_Repair,
All_Areas_Result.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Result_dated", acViewNormal
any suggestions?
thanks

kevin

Nov 14 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.