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
Fly Boy 5 said:
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!
Daryl S said:
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...
