SELECT QUERY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I created a simple select query for a single table only (as a checking
because I'm having a problem when I migle it with other tables). I put a
parameter in the date field and it did not return all "equivalent date".
When i checked my table (date format deleted), i noticed that those that
appears in my select query where only those that where exactly the same e.g.
"17-Aug-2005". Those that have time (e.g. "17-Aug-2005 12:00:00 PM") were
not included. I want to include all records that have "17-Aug-2005" without
regards to time. What should i do.
 
Try and use the format for both, the field and the parameter

Select * From TableName Where format([DateFieldName],"dd-mmm-yyyy") =
Format([Parameter],"dd-mmm-yyyy")
 
Jologs said:
Hi:

I created a simple select query for a single table only (as a checking
because I'm having a problem when I migle it with other tables). I put a
parameter in the date field and it did not return all "equivalent date".
When i checked my table (date format deleted), i noticed that those that
appears in my select query where only those that where exactly the same e.g.
"17-Aug-2005". Those that have time (e.g. "17-Aug-2005 12:00:00 PM") were
not included. I want to include all records that have "17-Aug-2005" without
regards to time. What should i do.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try:

PARAMETERS Forms!FormName!ControlName Date;
SELECT *
FROM table_name
WHERE DateValue(date_column) = Forms!FormName!ControlName

DateValue() extracts the date from the date/time value.

OR:

PARAMETERS Forms!FormName!ControlName Date;
SELECT *
FROM table_name
WHERE date_column BETWEEN Forms!FormName!ControlName AND
CDate(Forms!FormName!ControlName) + CDate("23:59:59")

The last query will probably be faster 'cuz the WHERE clause will use
the date_column's index (if it has one) and won't have to convert the
values of date_column for each row in the table (a table scan), which
takes longer than an index scan.

Translating the BETWEEN expression:

BETWEEN #17-Aug-2005# AND #17-Aug-2005 23:59:59#

This will return all rows that have a date of 17-Aug-2005 (from midnight
of 17-Aug-2005 to one second before midnight of 18-Aug-2005).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwOWAIechKqOuFEgEQIUEQCeIbcBfuwPOu/1isKk3LLSCvgQ2XkAoOu+
9obEx1Zv0CPWGEUSePTT43tr
=Y0Qq
-----END PGP SIGNATURE-----
 
Thanks to both of you. I have another concern, how will i be able to run a
select query with a paramater now(). I have tried "now()" in the criteria
but it returns no data. Thank again.
 
The now() returns the date with the time, this is why there a good chance no
records returned, use date() instead, with format on both fields

Where cvdate(Format(DateField,"dd/mm/yyyy")) =
cvdate(format(date(),"dd/mm/yyyy"))
 
Ofer said:
The now() returns the date with the time, this is why there a good chance no
records returned, use date() instead, with format on both fields

Where cvdate(Format(DateField,"dd/mm/yyyy")) =
cvdate(format(date(),"dd/mm/yyyy"))
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You don't have to use CVDate() & Format() on the column value 'cuz if
the column is a DateTime data type then the only thing that has to be a
date is the right side of the expression, which may require conversion.
Date() returns a "true" date value: a Double number representing the
number of days since Dec. 30, 1899 (the integer portion of the Double)
and the number of milliseconds since midnight of the indicated date (the
decimal portion of the Double). Therefore, the right side of the above
expression doesn't have to be converted.

Using a conversion on the left side of the expression (the column's
value) will slow down the query, because it will force the query to do a
table scan instead of just an index scan (assuming there is an index on
the column).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQwTYjoechKqOuFEgEQItowCeIx7/xPHxnAwcNKdC/z44zw3t9boAn379
yYjJv0O0tc8uAHF51wis0Qn/
=H0Qr
-----END PGP SIGNATURE-----
 
As Allan mantion in the first post, he stores the date and time in the date
field.

To compare this field with now most chanses it will return no records.
So he needs to compare it with date() instead, but because he stores the
time with the date, the filter will return no values because date() <> now().
So both fields need to be date with no time, and this is why I'm using the
format.

You right that there is no need for the cvdate where you compare format to
format, but just incase Allan decide to change it to < or > or between then
he will need the cvdate, either wise it will compare string and the result
wont be right.
 
Use a like statement in your criteria: Like *&[Enter date ##/##/####]&*
This will bring back just the date.
 
Back
Top