Date question formula

[Fly Boy 5]

I have a field for date of service and another field for invoice date. A
date range is entered for date of service and I need to allow invoice dates
between two dates.

I don't seem to be able to get the formula correct.

Please help!

Thanks

 

[Daryl S]

Fly Boy 5 -

If you are entering a date range into the DateOfService field, then it is
not a date field, and it will not be easy to compare the InvoiceDate to the
range. Instead, you should have two fields for DateOfService, maybe
DateOfServiceStart and DateOfServiceEnd. If these are true date fields (not
strings), then you can do a simple compare:

If InvoiceDate between DateOfServiceStart and DateOfServiceEnd Then
'all is good, proceed
Else
'error - InvoiceDate not in DateOfService range
End If

The compare above would depend on where you are doing the compare. If it is
on a form, then you need to use the control names. If you are in a query,
then you can use the IIf() construct instead of the If Then Else End If used
in the code.

If you need more help, provide more details...

 

[Fly Boy 5]

Hi Daryl,

I'm using a query and this is the formula to search the service date field.
Between [Type the beginning date:] And [Type the ending date:] This works
fine but there are overlaps on invoice dates. I need to be able to pick
invoice dates that are out of the first month so they show up on the next
months billing. I only add invoice dates that are out of the first range.

Sorry if I'm confusing things. Appreate your help!

 

[KARL DEWEY]

This works fine but there are overlaps on invoice dates.
Post sample data and what you want for query results.

--
Build a little, test a little.

Hi Daryl,

I'm using a query and this is the formula to search the service date field.
Between [Type the beginning date:] And [Type the ending date:] This works
fine but there are overlaps on invoice dates. I need to be able to pick
invoice dates that are out of the first month so they show up on the next
months billing. I only add invoice dates that are out of the first range.

Sorry if I'm confusing things. Appreate your help!

Fly Boy 5 -

If you are entering a date range into the DateOfService field, then it is
not a date field, and it will not be easy to compare the InvoiceDate to the
range. Instead, you should have two fields for DateOfService, maybe
DateOfServiceStart and DateOfServiceEnd. If these are true date fields (not
strings), then you can do a simple compare:

If InvoiceDate between DateOfServiceStart and DateOfServiceEnd Then
'all is good, proceed
Else
'error - InvoiceDate not in DateOfService range
End If

The compare above would depend on where you are doing the compare. If it is
on a form, then you need to use the control names. If you are in a query,
then you can use the IIf() construct instead of the If Then Else End If used
in the code.

If you need more help, provide more details...

 

[Daryl S]

Fly Boy 5 -

It sounds like your service date criteria (range) works fine, but you need
criteria for your invoice dates. I am not sure what you mean by needing
invoice dates that are "out of the first month".

If you need invoice dates before the first date in the service date range,
you can use this in the criteria for the invoice dates:
< [Type the beginning date:]
(Using the exact same parameter name does not make the user type it in twice).

If you need invoices that are prior to the month of the service date, then
you can use invoice criteria like this:
< DateSerial(Year([ServiceDate]),Month([ServiceDate]),1)

If you need invoices that are in the month of the start date of the service
date range, then you can use invoice criteria like this:

Between DateSerial(Year([Type the beginning date:]),Month([Type the
beginning date:]),1) AND DateSerial(Year([Type the beginning
date:]),Month([Type the beginning date:]) + 1,0)

If these aren't what you want, please tell us in words what the criteria
should be, and we can help with the formula.

--
Daryl S

Hi Daryl,

I'm using a query and this is the formula to search the service date field.
Between [Type the beginning date:] And [Type the ending date:] This works
fine but there are overlaps on invoice dates. I need to be able to pick
invoice dates that are out of the first month so they show up on the next
months billing. I only add invoice dates that are out of the first range.

Sorry if I'm confusing things. Appreate your help!

Fly Boy 5 -

If you are entering a date range into the DateOfService field, then it is
not a date field, and it will not be easy to compare the InvoiceDate to the
range. Instead, you should have two fields for DateOfService, maybe
DateOfServiceStart and DateOfServiceEnd. If these are true date fields (not
strings), then you can do a simple compare:

If InvoiceDate between DateOfServiceStart and DateOfServiceEnd Then
'all is good, proceed
Else
'error - InvoiceDate not in DateOfService range
End If

The compare above would depend on where you are doing the compare. If it is
on a form, then you need to use the control names. If you are in a query,
then you can use the IIf() construct instead of the If Then Else End If used
in the code.

If you need more help, provide more details...