Trouble calculating dates

[Afrosheen via AccessMonster.com]

Good afternoon,
I'm using a query called furlough with has a date field in it called [fdate]
I also have a config table called tblconfig which has a date field called
[EndDate]

I get the [EndDate] through the dlookup.

Private Sub Form_Open(Cancel As Integer)
Dim tDate, strwhere As String
tDate = Nz(DLookup("[EndDate]", "tblconfig"))
MsgBox tDate
strwhere = "[fdate] < '" & tDate & "'"
Filter = strwhere
FilterOn = True
End Sub

What I need to do is to compare the [fdate] to the [EndDate]. For example: I
need all records < 12/31/2010. I'm hoping that the filter will take care of
this. Both fields are date fields. I keep getting an error on FilterOn=true.

When I do a ?strwhere it shows [fdate]<12/31/2010, that's all.
Am I doing this correctly? If not can you point me in the right direction.

Thank you for your help.

 

[John W. Vinson]

Good afternoon,
I'm using a query called furlough with has a date field in it called [fdate]
I also have a config table called tblconfig which has a date field called
[EndDate]

I get the [EndDate] through the dlookup.

Private Sub Form_Open(Cancel As Integer)
Dim tDate, strwhere As String
tDate = Nz(DLookup("[EndDate]", "tblconfig"))
MsgBox tDate
strwhere = "[fdate] < '" & tDate & "'"
Filter = strwhere
FilterOn = True
End Sub

What I need to do is to compare the [fdate] to the [EndDate]. For example: I
need all records < 12/31/2010. I'm hoping that the filter will take care of
this. Both fields are date fields. I keep getting an error on FilterOn=true.

When I do a ?strwhere it shows [fdate]<12/31/2010, that's all.
Am I doing this correctly? If not can you point me in the right direction.

Thank you for your help.

For literal dates, you need to use # as a delimiter:

strWhere = "[Fdate] < #" & tDate & "#"

You're also Dim'ing tdate as a Variant, not a string. I'd dim it as a Date and
explicitly format it:

Dim tDate As Date, strWhere As String
tDate = DLookUp("[EndDate]", "tblConfig"
Me.Filter = "[fdate] < # & tDate & "#"
Me.FilterOn = True

 

[Mike Painter]

Good afternoon,
I'm using a query called furlough with has a date field in it called
[fdate] I also have a config table called tblconfig which has a date
field called [EndDate]

I get the [EndDate] through the dlookup.

Private Sub Form_Open(Cancel As Integer)
Dim tDate, strwhere As String
tDate = Nz(DLookup("[EndDate]", "tblconfig"))
MsgBox tDate
strwhere = "[fdate] < '" & tDate & "'"
Filter = strwhere
FilterOn = True
End Sub

What I need to do is to compare the [fdate] to the [EndDate]. For
example: I need all records < 12/31/2010. I'm hoping that the filter
will take care of this. Both fields are date fields. I keep getting
an error on FilterOn=true.

When I do a ?strwhere it shows [fdate]<12/31/2010, that's all.
Am I doing this correctly? If not can you point me in the right

Yes you are printing out the contents of a string which is not teh same as
evaluating it
? fdate < #12/31/2010# should give true or false.
Try strwhere = "[fdate] < #" & tDate & "#" to format tDate as a date.
I assume that fdate is a date type field.

 

[Marshall Barton]

I'm using a query called furlough with has a date field in it called [fdate]
I also have a config table called tblconfig which has a date field called
[EndDate]

I get the [EndDate] through the dlookup.

Private Sub Form_Open(Cancel As Integer)
Dim tDate, strwhere As String
tDate = Nz(DLookup("[EndDate]", "tblconfig"))
MsgBox tDate
strwhere = "[fdate] < '" & tDate & "'"
Filter = strwhere
FilterOn = True
End Sub

What I need to do is to compare the [fdate] to the [EndDate]. For example: I
need all records < 12/31/2010. I'm hoping that the filter will take care of
this. Both fields are date fields. I keep getting an error on FilterOn=true.

When I do a ?strwhere it shows [fdate]<12/31/2010, that's all.
Am I doing this correctly? If not can you point me in the right direction.

Access requires literal date/time values to be enclosed in #
signs. Actually, because of possible way to use local
settings in Windows, it's more complicated than that. The
recommended way to do it is like:

strwhere = "fdate < " & Format(tDate, "\#yyyy-m-d\#")

 

[Afrosheen via AccessMonster.com]

Thanks fellas. I put a couple of different dates in the EndDate field of the
tblconfig and it appears to be working ok. Now all I have to do is if the
date is beyond the enddate then "0" out the balance.

Thanks for the help. If you only knew how much I appreciate it.

I'm using a query called furlough with has a date field in it called [fdate]
I also have a config table called tblconfig which has a date field called

[quoted text clipped - 17 lines]

When I do a ?strwhere it shows [fdate]<12/31/2010, that's all.
Am I doing this correctly? If not can you point me in the right direction.

Access requires literal date/time values to be enclosed in #
signs. Actually, because of possible way to use local
settings in Windows, it's more complicated than that. The
recommended way to do it is like:

strwhere = "fdate < " & Format(tDate, "\#yyyy-m-d\#")

 

[Douglas J. Steele]

For literal dates, you need to use # as a delimiter:

strWhere = "[Fdate] < #" & tDate & "#"

<picky>

That's not guaranteed to work if the user's default date format is something
other than mm/dd/yyyy (or yyyy-mm-dd).

Since you can't be sure, you're better off using

strWhere = "[Fdate] < " & Format(tDate, "\#yyyy\-mm\-dd\#")

</picky>

 

[Mike Painter]

For literal dates, you need to use # as a delimiter:

strWhere = "[Fdate] < #" & tDate & "#"

<picky>

That's not guaranteed to work if the user's default date format is
something other than mm/dd/yyyy (or yyyy-mm-dd).

Since you can't be sure, you're better off using

strWhere = "[Fdate] < " & Format(tDate, "\#yyyy\-mm\-dd\#")

</picky>

Picky until it stops working every once in a while.
I recently moved a subform related to another subform onto a tab control and
it stopped working. It had worked for years. The linking control started
showing "Name" I fiddled, it worked for a while, then it went belly up
again.

I was halfway through a plea for help when it struck me that the unbound
control on the form was named I commonly used for a field name. I changed
it and all was well with the world.

Sometimes I think Access tries to hard.

 

[Afrosheen via AccessMonster.com]

Hi Doug,
I set up the format to:
Me.Filter = "[fdate] <= " & Format(tDate, "\#mm\-dd\-yyyy\#")

I'm in the US and usually that's the way they enter the date {unless you're
in the military}. I also have the input mask set for the short date

Thanks for all you're help..

For literal dates, you need to use # as a delimiter:

strWhere = "[Fdate] < #" & tDate & "#"

<picky>

That's not guaranteed to work if the user's default date format is something
other than mm/dd/yyyy (or yyyy-mm-dd).

Since you can't be sure, you're better off using

strWhere = "[Fdate] < " & Format(tDate, "\#yyyy\-mm\-dd\#")

</picky>

 

[Douglas J. Steele]

Glad you got it working.

For the record, dates are not stored with a format: they're 8 byte floating
point numbers where the integer portion represents the date as the number of
days relative to 30 Dec, 1899 and the decimal portion represents the time as
a fraction of a day. The format I suggested (which is the ISO standard)
would work just as well for you.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Hi Doug,
I set up the format to:
Me.Filter = "[fdate] <= " & Format(tDate, "\#mm\-dd\-yyyy\#")

I'm in the US and usually that's the way they enter the date {unless
you're
in the military}. I also have the input mask set for the short date

Thanks for all you're help..

For literal dates, you need to use # as a delimiter:

strWhere = "[Fdate] < #" & tDate & "#"

<picky>

That's not guaranteed to work if the user's default date format is
something
other than mm/dd/yyyy (or yyyy-mm-dd).

Since you can't be sure, you're better off using

strWhere = "[Fdate] < " & Format(tDate, "\#yyyy\-mm\-dd\#")

</picky>

 

[Afrosheen via AccessMonster.com]

Thanks for the information Doug. I really appreciate it.
I'll try and convert the people I'm writing this for. Don't know if it will
work though.

Glad you got it working.

For the record, dates are not stored with a format: they're 8 byte floating
point numbers where the integer portion represents the date as the number of
days relative to 30 Dec, 1899 and the decimal portion represents the time as
a fraction of a day. The format I suggested (which is the ISO standard)
would work just as well for you.

Hi Doug,
I set up the format to:

[quoted text clipped - 21 lines]