Date search problem

[kevcar40]

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

[Allen Browne]

Re: Date search problem

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" <kevcar40@btint ernet.com> wrote in message
news:1131877667 .309710.216460@ g44g2000cwa.goo glegroups.com.. .[color=blue]
>
> 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[/color]

[lylefair]

Re: Date search problem


Allen Browne wrote:[color=blue]
> 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[/color]

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.

[osmethod@eircom.net]

Re: Date search problem

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

[lylefair]

Re: Date search problem

Try this code in a modern version of Access:

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

[Allen Browne]

Re: Date search problem

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" <lylefairfield@ aim.com> wrote in message
news:1131900497 .631541.227120@ g49g2000cwa.goo glegroups.com.. .[color=blue]
>
> Allen Browne wrote:[color=green]
>> 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[/color]
>
> 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.[/color]

[kevcar40]

Re: Date search problem

thanks all
i will try your suggestions and get back if any further problems
thanks again

[kevcar40]

Re: Date search problem

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_Resul t.Identified_Re pair,All_Areas_ Result.Area,sum (All_Areas_Resu lt.Quantity)
as Quantity " & _
"FROM All_Areas_Resul t " & _
"WHERE All_Areas_Resul t.Dates Between" &
SQLDate(Me.txtS tartDate.Value) & "and " & SQLDate(Me.txtE ndDate.Value)
& _
"GROUP BY All_Areas_Resul t.Identified_Re pair,
All_Areas_Resul t.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Resu lt_dated", acViewNormal

i have also tried
this code crashes

strSQL = "SELECT All_Areas_Resul t.Identified_Re pair,
All_Areas_Resul t.Area, sum(All_Areas_R esult.Quantity) as Quantity " & _
"FROM All_Areas_Resul t " & _
"WHERE All_Areas_Resul t.Dates Between # &
Me.txtStartDate .Value # and # Me.txtEndDate.V alue # & _
"and [Area] <> 'Head'" & _
"and [Area] <> 'Hot Test'" & _
"GROUP BY All_Areas_Resul t.Identified_Re pair,
All_Areas_Resul t.Area"
qdf.SQL = strSQL
DoCmd.OpenQuery "All_Areas_Resu lt_dated", acViewNormal
any suggestions?


thanks

kevin

[Allen Browne]

Re: Date search problem

If you open table All_Areas_Resul t 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" <kevcar40@btint ernet.com> wrote in message
news:1131979919 .623901.105800@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> 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_Resul t.Identified_Re pair,All_Areas_ Result.Area,sum (All_Areas_Resu lt.Quantity)
> as Quantity " & _
> "FROM All_Areas_Resul t " & _
> "WHERE All_Areas_Resul t.Dates Between" &
> SQLDate(Me.txtS tartDate.Value) & "and " & SQLDate(Me.txtE ndDate.Value)
> & _
> "GROUP BY All_Areas_Resul t.Identified_Re pair,
> All_Areas_Resul t.Area"
> qdf.SQL = strSQL
> DoCmd.OpenQuery "All_Areas_Resu lt_dated", acViewNormal
>
> i have also tried
> this code crashes
>
> strSQL = "SELECT All_Areas_Resul t.Identified_Re pair,
> All_Areas_Resul t.Area, sum(All_Areas_R esult.Quantity) as Quantity " & _
> "FROM All_Areas_Resul t " & _
> "WHERE All_Areas_Resul t.Dates Between # &
> Me.txtStartDate .Value # and # Me.txtEndDate.V alue # & _
> "and [Area] <> 'Head'" & _
> "and [Area] <> 'Hot Test'" & _
> "GROUP BY All_Areas_Resul t.Identified_Re pair,
> All_Areas_Resul t.Area"
> qdf.SQL = strSQL
> DoCmd.OpenQuery "All_Areas_Resu lt_dated", acViewNormal
> any suggestions?
>
>
> thanks
>
> kevin[/color]