Comparing Dates

  • Thread starter Thread starter Public
  • Start date Start date
P

Public

Hi,

I want to compare two dates. I have a date field from a table and I want to
compare it with another date (say DateB). The problem is that DateB's day and
month parts are constant but the year part is coming from a form. How can I
write the complete DateB. I have tried writing something like
1/1/[Forms]!etc. and it did not accept that.

Any ideas?

Regards
 
It all starts with the data.

I have no idea what data you are actually storing in your tables, so it
could be tough offering specific "how to's" ...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have something like CourseStartDate in my DB. I want to compare it with 01
Jan but the year part should come from a form. So, if the user selects 2006
in the form, the query should show the result of
CourseStartDate = 01/01/2006
And if the user selects 2007 in the form, then the query should show the
result of
CourseStartDAte = 01/01/2007
And so on

Regards


Jeff Boyce said:
It all starts with the data.

I have no idea what data you are actually storing in your tables, so it
could be tough offering specific "how to's" ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Public said:
Hi,

I want to compare two dates. I have a date field from a table and I want
to
compare it with another date (say DateB). The problem is that DateB's day
and
month parts are constant but the year part is coming from a form. How can
I
write the complete DateB. I have tried writing something like
1/1/[Forms]!etc. and it did not accept that.

Any ideas?

Regards
Click to expand...
Click to expand...
 
So you are saying that if the year-portion of a date matches the value in
the form, select that record?

Take a look at the Year() function to create a new field in your query, and
use your reference to your form's value as the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Public said:
I have something like CourseStartDate in my DB. I want to compare it with
01
Jan but the year part should come from a form. So, if the user selects
2006
in the form, the query should show the result of
CourseStartDate = 01/01/2006
And if the user selects 2007 in the form, then the query should show the
result of
CourseStartDAte = 01/01/2007
And so on

Regards


Jeff Boyce said:
It all starts with the data.

I have no idea what data you are actually storing in your tables, so it
could be tough offering specific "how to's" ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Public said:
Hi,

I want to compare two dates. I have a date field from a table and I
want
to
compare it with another date (say DateB). The problem is that DateB's
day
and
month parts are constant but the year part is coming from a form. How
can
I
write the complete DateB. I have tried writing something like
1/1/[Forms]!etc. and it did not accept that.

Any ideas?

Regards
Click to expand...
Click to expand...
Click to expand...
 
You don't indicate what type of comparison you want to make, so I'll assume
that you want your query to return all of the records from your Courses table
where the start date is >= 01/01/2006 (where the 2006 comes from a combo box
on your form). The select statement might look like:

SELECT * FROM tbl_Courses
WHERE [CourseStartDate] >
Dateserial(Forms!yourFormName.cbo_CourseYear, 1, 1)



--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Public said:
I have something like CourseStartDate in my DB. I want to compare it with 01
Jan but the year part should come from a form. So, if the user selects 2006
in the form, the query should show the result of
CourseStartDate = 01/01/2006
And if the user selects 2007 in the form, then the query should show the
result of
CourseStartDAte = 01/01/2007
And so on

Regards


Jeff Boyce said:
It all starts with the data.

I have no idea what data you are actually storing in your tables, so it
could be tough offering specific "how to's" ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Public said:
Hi,

I want to compare two dates. I have a date field from a table and I want
to
compare it with another date (say DateB). The problem is that DateB's day
and
month parts are constant but the year part is coming from a form. How can
I
write the complete DateB. I have tried writing something like
1/1/[Forms]!etc. and it did not accept that.

Any ideas?

Regards
Click to expand...
Click to expand...
Click to expand...
 
The following expression should work to give you a date value you can use in a
query.

Where CourseStartDate = DateSerial(Forms!FormName!ControlNameForYear,1,1)

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top