Compare Date in Table to Date Entered in Form

[Guest]

Greetings.

I am writing a query that looks up escorts trained on a certain day.

When an escort is trained, his training date is tracked inn the [All Escorts
tbl]
in the Trained field. The Trained field is defined as a date/time field
with a
format of medium date.

When somebody wishes to query all the escorts trained on a certain day,
he enters their training date in the DateTxt field of [Find Escorts frm].
He then
issues a query. DateTxt is defined as a text box with a medium date format.

The problem is that the query does not seem to be able to accurately compare
the Training field in the table with the DateTxt field in the form and I'm not
able to successfully look up the date.

When I query on the name, which can also be entered as a separate field
in the form, I don't have a problem.

Here's the comparison that I'm attempting to make in the query:

[All Escorts tbl].[Trained]=[Forms]![Find Escorts frm]![DateTxt]

I know that date comparisons can be tricky. Does anybody know how I can
modify this comparison to make is work correctly and select those records
where the dates are equal?

 

[John Spencer]

This could be caused by a few things.
One: Access is not recognizing the control reference as a date but thinks it
is math statement and is dividing to get a small fractional number
Two: Your date field contains a time component, even though it is not
displaying it.

Try the following to force Access to handle DateTxt as a date field
[All Escorts tbl].[Trained]=CDate([Forms]![Find Escorts frm]![DateTxt])

If that doesn't return records then try the following to strip off any time
in the field Trained
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find Escorts
frm]![DateTxt])

If that still fails, then post back and post the SQL text of your query.

 

[Guest]

Hi John:

I'm sorry to say that neither idea worked.

Here's the complete SQL statement:

INSERT INTO [My Escorts tbl]
SELECT [All Escorts tbl].*
FROM [My Escorts tbl] RIGHT JOIN [All Escorts tbl] ON [My Escorts tbl].ID =
[All Escorts tbl].ID
WHERE ((([My Escorts tbl].ID) Is Null)
AND ((IIf([Forms]![Find Escorts frm]![LastName] Is Null,
[All Escorts tbl].[Last Name] Like '*' Or [All Escorts
tbl]![Last Name] Is Null,
[All Escorts tbl].[Last Name]=[Forms]![Find Escorts
frm]![LastName]))<>False)
AND ((IIf([Forms]![Find Escorts frm]![FirstName] Is Null,
[All Escorts tbl].[First Name] Like '*' Or [All Escorts
tbl].[First Name] Is Null,
[All Escorts tbl].[First Name]=[Forms]![Find Escorts
frm]![FirstName]))<>False)
AND ((IIf([Forms]![Find Escorts frm]![DateTxt] Is Null,
[All Escorts tbl].[Trained] Like '*' Or [All Escorts
tbl].[Trained] Is Null,
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find
Escorts frm]![DateTxt])))<>False));

As I mentioned before, the First Name and Last Name parts of the query work
fine but I can't get the date to work.

This could be caused by a few things.
One: Access is not recognizing the control reference as a date but thinks it
is math statement and is dividing to get a small fractional number
Two: Your date field contains a time component, even though it is not
displaying it.

Try the following to force Access to handle DateTxt as a date field
[All Escorts tbl].[Trained]=CDate([Forms]![Find Escorts frm]![DateTxt])

If that doesn't return records then try the following to strip off any time
in the field Trained
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find Escorts
frm]![DateTxt])

If that still fails, then post back and post the SQL text of your query.

Greetings.

I am writing a query that looks up escorts trained on a certain day.

When an escort is trained, his training date is tracked inn the [All
Escorts
tbl]
in the Trained field. The Trained field is defined as a date/time field
with a
format of medium date.

When somebody wishes to query all the escorts trained on a certain day,
he enters their training date in the DateTxt field of [Find Escorts frm].
He then
issues a query. DateTxt is defined as a text box with a medium date
format.

The problem is that the query does not seem to be able to accurately
compare
the Training field in the table with the DateTxt field in the form and I'm
not
able to successfully look up the date.

When I query on the name, which can also be entered as a separate field
in the form, I don't have a problem.

Here's the comparison that I'm attempting to make in the query:

[All Escorts tbl].[Trained]=[Forms]![Find Escorts frm]![DateTxt]

I know that date comparisons can be tricky. Does anybody know how I can
modify this comparison to make is work correctly and select those records
where the dates are equal?

 

[John Spencer]

I might try the following modification to your query. In all honesty I
would probably be using VBA to build the where clause on this. And what I
really don't know is your need to populate an identical table with the data.

INSERT INTO [My Escorts tbl]
SELECT [All Escorts tbl].*
FROM [My Escorts tbl] RIGHT JOIN [All Escorts tbl]
ON [My Escorts tbl].ID = [All Escorts tbl].ID
WHERE [My Escorts tbl].ID Is Null
AND IIf([Forms]![Find Escorts frm]![LastName] Is Null,
[All Escorts tbl].[Last Name] Like '*' Or [All Escorts
tbl]![Last Name] Is Null,
[All Escorts tbl].[Last Name]=[Forms]![Find Escorts
frm]![LastName])<>False

AND IIf([Forms]![Find Escorts frm]![FirstName] Is Null,
[All Escorts tbl].[First Name] Like '*' Or [All Escorts tbl].[First
Name] Is Null,
[All Escorts tbl].[First Name]=[Forms]![Find Escorts
frm]![FirstName]))<>False

AND IIf([Forms]![Find Escorts frm]![DateTxt] Is Null,
[All Escorts tbl].[Trained] =[All Escorts tbl].[Trained] Or [All
Escorts tbl].[Trained] Is Null,
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find Escorts
frm]![DateTxt])<>False

Hi John:

I'm sorry to say that neither idea worked.

Here's the complete SQL statement:

INSERT INTO [My Escorts tbl]
SELECT [All Escorts tbl].*
FROM [My Escorts tbl] RIGHT JOIN [All Escorts tbl] ON [My Escorts tbl].ID
=
[All Escorts tbl].ID
WHERE ((([My Escorts tbl].ID) Is Null)
AND ((IIf([Forms]![Find Escorts frm]![LastName] Is Null,
[All Escorts tbl].[Last Name] Like '*' Or [All Escorts
tbl]![Last Name] Is Null,
[All Escorts tbl].[Last Name]=[Forms]![Find Escorts
frm]![LastName]))<>False)
AND ((IIf([Forms]![Find Escorts frm]![FirstName] Is Null,
[All Escorts tbl].[First Name] Like '*' Or [All Escorts
tbl].[First Name] Is Null,
[All Escorts tbl].[First Name]=[Forms]![Find Escorts
frm]![FirstName]))<>False)
AND ((IIf([Forms]![Find Escorts frm]![DateTxt] Is Null,
[All Escorts tbl].[Trained] Like '*' Or [All Escorts
tbl].[Trained] Is Null,
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find
Escorts frm]![DateTxt])))<>False));

As I mentioned before, the First Name and Last Name parts of the query
work
fine but I can't get the date to work.

This could be caused by a few things.
One: Access is not recognizing the control reference as a date but thinks
it
is math statement and is dividing to get a small fractional number
Two: Your date field contains a time component, even though it is not
displaying it.

Try the following to force Access to handle DateTxt as a date field
[All Escorts tbl].[Trained]=CDate([Forms]![Find Escorts frm]![DateTxt])

If that doesn't return records then try the following to strip off any
time
in the field Trained
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find Escorts
frm]![DateTxt])

If that still fails, then post back and post the SQL text of your query.


message

Greetings.

I am writing a query that looks up escorts trained on a certain day.

When an escort is trained, his training date is tracked inn the [All
Escorts
tbl]
in the Trained field. The Trained field is defined as a date/time
field
with a
format of medium date.

When somebody wishes to query all the escorts trained on a certain day,
he enters their training date in the DateTxt field of [Find Escorts
frm].
He then
issues a query. DateTxt is defined as a text box with a medium date
format.

The problem is that the query does not seem to be able to accurately
compare
the Training field in the table with the DateTxt field in the form and
I'm
not
able to successfully look up the date.

When I query on the name, which can also be entered as a separate field
in the form, I don't have a problem.

Here's the comparison that I'm attempting to make in the query:

[All Escorts tbl].[Trained]=[Forms]![Find Escorts frm]![DateTxt]

I know that date comparisons can be tricky. Does anybody know how I
can
modify this comparison to make is work correctly and select those
records
where the dates are equal?

 

[Guest]

John:

Ignore my last posting. I discovered an error in my form design which
prevented a proper date comparison. I've corrected the error and now
everthing works fine.

Thanks for your assistance.

This could be caused by a few things.
One: Access is not recognizing the control reference as a date but thinks it
is math statement and is dividing to get a small fractional number
Two: Your date field contains a time component, even though it is not
displaying it.

Try the following to force Access to handle DateTxt as a date field
[All Escorts tbl].[Trained]=CDate([Forms]![Find Escorts frm]![DateTxt])

If that doesn't return records then try the following to strip off any time
in the field Trained
DateValue([All Escorts tbl].[Trained])=CDate([Forms]![Find Escorts
frm]![DateTxt])

If that still fails, then post back and post the SQL text of your query.

Greetings.

I am writing a query that looks up escorts trained on a certain day.

When an escort is trained, his training date is tracked inn the [All
Escorts
tbl]
in the Trained field. The Trained field is defined as a date/time field
with a
format of medium date.

When somebody wishes to query all the escorts trained on a certain day,
he enters their training date in the DateTxt field of [Find Escorts frm].
He then
issues a query. DateTxt is defined as a text box with a medium date
format.

The problem is that the query does not seem to be able to accurately
compare
the Training field in the table with the DateTxt field in the form and I'm
not
able to successfully look up the date.

When I query on the name, which can also be entered as a separate field
in the form, I don't have a problem.

Here's the comparison that I'm attempting to make in the query:

[All Escorts tbl].[Trained]=[Forms]![Find Escorts frm]![DateTxt]

I know that date comparisons can be tricky. Does anybody know how I can
modify this comparison to make is work correctly and select those records
where the dates are equal?