Best way to Query

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

My database originates with an imported comma delimited
textfile. The field that I really need to "query" is the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the date away
from the time and it appears to work, but then a query
using a date entered still does not return results. The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks
 
Joe,

Could you post a sample of the data that you have and a sample of the
results that you want. The better we can understand your question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) = #12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.
 
-----Original Message-----
Joe,

Could you post a sample of the data that you have and a sample of the
results that you want. The better we can understand your question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) = #12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

My database originates with an imported comma delimited
textfile. The field that I really need to "query" is the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the date away
from the time and it appears to work, but then a query
using a date entered still does not return results. The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks
Click to expand...


.
Trev,
Click to expand...
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
F3207700 CMP 12/15/2003 6:58:00 AM
F3207800 OPN
F3207900 CMP 12/4/2003 7:08:00 AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
thanks again.
 
Joe,

Judging by your sample, you just want to filter on the date, not the date
time.

There are various ways of doing this, but I tend to strip the decimal points
off the date by changing it to a Long and then back into a date. In your
example, you could do it by entering the following SQL into the (change
"Table1" to the name of your table):

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex]))=[@EnteredDate];

If you want to change this around to include a range of dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex])) BETWEEN [@StartDate] AND [@EndDate];


Hope this helps,

Trev.


Joe said:
-----Original Message-----
Joe,

Could you post a sample of the data that you have and a sample of the
results that you want. The better we can understand your question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) = #12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

My database originates with an imported comma delimited
textfile. The field that I really need to "query" is the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the date away
from the time and it appears to work, but then a query
using a date entered still does not return results. The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks
Click to expand...


.
Trev,
Click to expand...
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
F3207700 CMP 12/15/2003 6:58:00 AM
F3207800 OPN
F3207900 CMP 12/4/2003 7:08:00 AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
thanks again.
Click to expand...
 
Sorry, Ignore the solution in the last post - it messes up for times after
12pm because of decimal rounding. As the great philosopher Homer would say:
D'Oh!

Use the following instead (I've actually tested it this time!)

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(Format([OnComplex], "dd/MM/yyyy"))=[@EnteredDate];

If you want to change this around to include a range of dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(Format([OnComplex], "dd/MM/yyyy"))BETWEEN [@StartDate] AND
[@EndDate];


If you want the user to only enter a month and year (e.g. 12/2003), then
change the format string in the Format Function to "MM/yyyy" instead of
"dd/MM/yyyy".

Hope this helps,

Trev.
 
-----Original Message-----
Joe,

Judging by your sample, you just want to filter on the date, not the date
time.

There are various ways of doing this, but I tend to strip the decimal points
off the date by changing it to a Long and then back into a date. In your
example, you could do it by entering the following SQL into the (change
"Table1" to the name of your table):

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex]))=[@EnteredDate];

If you want to change this around to include a range of dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex])) BETWEEN [@StartDate] AND [@EndDate];


Hope this helps,

Trev.


-----Original Message-----
Joe,

Could you post a sample of the data that you have and a sample of the
results that you want. The better we can understand
Click to expand...
your
question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) = #12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

My database originates with an imported comma delimited
textfile. The field that I really need to "query" is the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the date away
from the time and it appears to work, but then a query
using a date entered still does not return results. The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks


.
Trev,
Click to expand...
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
F3207700 CMP 12/15/2003 6:58:00 AM
F3207800 OPN
F3207900 CMP 12/4/2003 7:08:00 AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
thanks again.
Click to expand...


.
Trev,
Click to expand...
Where would I plug in the SQL statements you suggested? I
know this is something I should already know, but duhhhh...
Thanks
Joe
 
Joe,

In Access, Open a new query in Design Mode. Then Go to the "View" Menu ->
"SQL View". Simply paste the text there.

Remember to use the fixed version of the code I posted - not the one that
used Cdate(Clng([Field])) !

Hope this helps,

Trev.

Joe said:
-----Original Message-----
Joe,

Judging by your sample, you just want to filter on the date, not the date
time.

There are various ways of doing this, but I tend to strip the decimal points
off the date by changing it to a Long and then back into a date. In your
example, you could do it by entering the following SQL into the (change
"Table1" to the name of your table):

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex]))=[@EnteredDate];

If you want to change this around to include a range of dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex])) BETWEEN [@StartDate] AND [@EndDate];


Hope this helps,

Trev.


-----Original Message-----
Joe,

Could you post a sample of the data that you have and a
sample of the
results that you want. The better we can understand your
question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) =
#12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

message
My database originates with an imported comma delimited
textfile. The field that I really need to "query" is
the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the date away
from the time and it appears to work, but then a query
using a date entered still does not return results. The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks


.
Trev,
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
F3207700 CMP 12/15/2003 6:58:00 AM
F3207800 OPN
F3207900 CMP 12/4/2003 7:08:00 AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003 8:32:00 AM
F3207600 CMP 12/8/2003 6:36:00 AM
thanks again.
Click to expand...


.
Trev,
Click to expand...
Where would I plug in the SQL statements you suggested? I
know this is something I should already know, but duhhhh...
Thanks
Joe
Click to expand...
 
Trev,
Found the "SQL View" of the query, thanks.
I am getting the Microsoft! error message:
"....expression is typed incorrectly or is too
complex...on and on and on....."
checked my naming and spacing over and over. dont really
know what else could be wrong with it??
-----Original Message-----
Joe,

In Access, Open a new query in Design Mode. Then Go to the "View" Menu ->
"SQL View". Simply paste the text there.

Remember to use the fixed version of the code I posted - not the one that
used Cdate(Clng([Field])) !

Hope this helps,

Trev.

-----Original Message-----
Joe,

Judging by your sample, you just want to filter on the date, not the date
time.

There are various ways of doing this, but I tend to
Click to expand...
strip
the decimal points
off the date by changing it to a Long and then back
Click to expand...
into
a date. In your
example, you could do it by entering the following SQL into the (change
"Table1" to the name of your table):

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex]))=[@EnteredDate];

If you want to change this around to include a range of dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex])) BETWEEN [@StartDate] AND [@EndDate];


Hope this helps,

Trev.



-----Original Message-----
Joe,

Could you post a sample of the data that you have and a
sample of the
results that you want. The better we can understand your
question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) =
#12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

message
My database originates with an imported comma delimited
textfile. The field that I really need to "query" is
the
datatype General Date/Time like this "12/12/2003 12:50
PM". I have tried to use a query to split the
Click to expand...
date
away
from the time and it appears to work, but then a query
using a date entered still does not return
Click to expand...
results.
The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks


.
Trev,
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003
Click to expand...
8:32:00
AM
F3207600 CMP 12/8/2003
Click to expand...
6:36:00
AM
F3207700 CMP 12/15/2003
Click to expand...
6:58:00
AM
F3207800 OPN
F3207900 CMP 12/4/2003
Click to expand...
7:08:00
AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003
Click to expand...
8:32:00
AM
F3207600 CMP 12/8/2003
Click to expand...
6:36:00
AM
thanks again.



.
Trev,
Click to expand...
Where would I plug in the SQL statements you suggested? I
know this is something I should already know, but duhhhh...
Thanks
Joe
Click to expand...


.
Click to expand...
 
Humm, I hate to use the dreaded "it works on my machine" ;), but are you
sure that all brackets (square and round) match up? Are all Table names and
Field names correct? Do you have the SemiColon after the Parameters and the
end of the query?

Which version of Access are you using?

If you don't get it sorted, post the structure of your table and I'll taylor
the query to match that exactly - you can then copy and paste the code
directly.

Trev.


Joe said:
Trev,
Found the "SQL View" of the query, thanks.
I am getting the Microsoft! error message:
"....expression is typed incorrectly or is too
complex...on and on and on....."
checked my naming and spacing over and over. dont really
know what else could be wrong with it??
-----Original Message-----
Joe,

In Access, Open a new query in Design Mode. Then Go to the "View" Menu ->
"SQL View". Simply paste the text there.

Remember to use the fixed version of the code I posted - not the one that
used Cdate(Clng([Field])) !

Hope this helps,

Trev.

-----Original Message-----
Joe,

Judging by your sample, you just want to filter on the
date, not the date
time.

There are various ways of doing this, but I tend to strip
the decimal points
off the date by changing it to a Long and then back into
a date. In your
example, you could do it by entering the following SQL
into the (change
"Table1" to the name of your table):

PARAMETERS [@EnteredDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex]))=[@EnteredDate];

If you want to change this around to include a range of
dates (again
ignoring the time part):

PARAMETERS [@StartDate] DateTime, [@EndDate] DateTime;
SELECT Table1.*
FROM Table1
WHERE CDate(CLng([OnComplex])) BETWEEN [@StartDate] AND
[@EndDate];


Hope this helps,

Trev.


message

-----Original Message-----
Joe,

Could you post a sample of the data that you have and a
sample of the
results that you want. The better we can understand
your
question, the
better the answers will be ;)

E.g

Table1
---------
TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street
13/12/2003 13:50 John Smith 1 Smith Street

Query 1 (People on 12/12/2003)
-------------------------------
SELECT * from Table1 where cdate(clng([TheDate])) =
#12/12/2003#

TheDate TheName TheAddress
12/12/2003 12:50 Joe Bloggs 1 Joe Street


Trev.

message
My database originates with an imported comma
delimited
textfile. The field that I really need to "query" is
the
datatype General Date/Time like this "12/12/2003
12:50
PM". I have tried to use a query to split the date
away
from the time and it appears to work, but then a
query
using a date entered still does not return results.
The
DateValue() function only resulted in syntax error.
My "users" need to be able to enter a date and be
shown
every record of that date, regardless of time of day.
Anyone want to stab at this......thanks


.
Trev,
Here are some samples;
Textfile = F3207500,CMP,12/8/2003 8:32:00 AM,
F3207600,CMP,12/8/2003 6:36:00 AM,
F3207700,PUT,12/15/2003 6:58:00 AM,
Access Table = PO# Status OnComplex
(Datatype) (Text) (Text) (Date/Time)
F3207500 CMP 12/8/2003 8:32:00
AM
F3207600 CMP 12/8/2003 6:36:00
AM
F3207700 CMP 12/15/2003 6:58:00
AM
F3207800 OPN
F3207900 CMP 12/4/2003 7:08:00
AM
Desired result = User entered date "12/8/2003"
Should show all records for 12/8/2003.....
F3207500 CMP 12/8/2003 8:32:00
AM
F3207600 CMP 12/8/2003 6:36:00
AM
thanks again.



.
Trev,
Where would I plug in the SQL statements you suggested? I
know this is something I should already know, but duhhhh...
Thanks
Joe
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top