Most efficient way of limiting data in fields. 1

[sara82]

I have posted a question earlier about limiting date in a field.
I have 3 date fields Date Delivered, Date Received, and Date Approved

I began using

(Between [Enter Date Delivered Start Date] And [Enter Date Delivered End Date]) Or [Enter Date Delivered Start Date] Is Null Or [Enter Date Delivered End Date] Is Null

(Between [Enter Date Received Start Date] And [Enter Date Received End Date]) Or [Enter Date Received Start Date] Is Null Or [Enter Date Received End Date] Is Null

(Between [Enter Date Approved Start Date] And [Enter Date Approved End Date]) Or [Enter Date Approved Start Date] Is Null Or [Enter Date Approved End Date] Is Null

in the criteria row of the date field in my query.

When I run the query, I obtain the correct results.

When I run the reports, my results are all incorrect. I’m not sure what I should be doing.
And since I have 3 date fields, 6 parameter boxes will pop up.

What’s the most efficient way I can limit my reports according to these 3 date fields?

 

[misscrf]

you want the date limited to between 3 dates? I wonder if 2 of those dates encompass 1 of them. What I mean is if the 3 dates will always be

date delivered - always earliest (01/01/2005)

date received - always 2nd/in middle of range (03/01/2005)

date approved - always last (05/01/2005)

Then your formula would actually be between date delivered and date approved.

If you are looking for in between delivered and received OR received and approved. I would do delivered -approved 2 times in the query. One time with > and one with < of the received date. Let me know if you need me to explain my gibberish. lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sara82]

I created this form:

This is the code for the form for right now:

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdPreview_Click()
Me.Visible = False
End Sub

For one of my reports I have the following code:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmWhatDates", , , , , acDialog, "frmWhatDates"
End Sub

Private Sub Report_Close()
DoCmd.Close acForm, "frmWhatDates"
End Sub

I don't know how or where to place code to filter for the date ranges for these 3 date fields.

 

[misscrf]

For the report that you are running..

Go to the control source that it is based on, bring up the query design

If you have 1 date field in the report's query (control source) for received, one for shipped and so on:

Then it will be the appropriate send receive approve for the following under each fields criteria:

Between [form].[formname].[formfieldstart] and [form].[formname].[formfieldend]

----

In the report properties, I would also put this:
(you can of course customize the message and title.)

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Err_Report_NoData:
msgbox "There is no data for the date ranges entered!", vbOKOnly, "Please enter wider date ranges to gather your data"
Cancel = True

Exit_Report_NoData:
    Exit Sub

Err_Report_NoData:
Resume Exit_Report_NoData
End Sub

obivously goes in the onnodata event of the report.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sara82]

Thank you misscrf

When I put in the critera row of the query

Between [form].[frmWhatDates].[txtRecvStart] and [form].[frmWhatDates].[txtRecvEnd]

and I run the report

my frmWhatDates form comes up but then a

a parameter pops up

Form.frm2Dates.txtRecvStart

and then another parameter pops up

Form.frm2Dates.txtRecvEnd

the dates aren't filtered according to the form but to the parameters...

am i doing something wrong.. and why did parameters come up?

 

[ProgramError]

Try

Between [forms]![frmWhatDates].[txtRecvStart] and [forms]![frmWhatDates].[txtRe
cvEnd]

and let us know the outcome.

Program Error
Programmers do it one finger at a time!

 

[ProgramError]

one other thing...

Are you querying on all three start/end date criteria or are you wanting to query on one of the start/end date and leaving the other two blank?

nice form by the way, but i personally would remove the record selector on the left hand side. you'll find it in properties/format/record selectors

Program Error
Programmers do it one finger at a time!

 

[RoyVidar]

It will usually take bangs (!) in queries, and the "plural" reference (Forms), as noted by ProgramError.

[tt]... [forms]![frmWhatDates]![txtRecvStart] ...[/tt]

Roy-Vidar

 

[ProgramError]

opps


Program Error
Programmers do it one finger at a time!

 

[misscrf]

yes, I agree with the bangs ! thats why I use the expression builder to make my criterias. I will type between, then choose the form field from the folders below. That way the syntax is done for me.

Your post sounded like somewhere on your query or report, there was a criteria placed for Form.frm2Dates.txtRecvStart
and Form.frm2Dates.txtRecvEnd . I would look around and make sure that if you didnt want those in the query or report, that you remove them.

Like I said also, if you want all 6 parameters answered at once ( difficult to contruct) It will end up having some parenthases to include everything right

(Between date1 and date2) AND (Between date3 and date4) AND (between date5 and date6)

play with the parentheses to get right syntax on that.

let us know how it goes.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sara82]

Thanks everyone for your help!I have my form where you enter dates now working.

ProgramError:
There are times where I will be querying all there start/end dates when needed and also sometimes needed to query one of them and leaving the other two blank or querying two of them and leaving one blank..

So what I did was add the is Null

What I did was type in the Critera of the Date Received field:

(Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) Or [forms]![frmWhatDates].[txtRecvStart]Is Null Or [forms]![frmWhatDates].[txtRecvEnd] Is Null

I did the same for Date Delivered and Date Approved for the critera.

When I closed my query and opened it up again it has all this additonal info in the critera sections because I added the Is Null. Everything is working though..I am getting accurate results. Just wondering if I did the Is Null correctly.

 

[misscrf]

I would not know what to say at this point. I just have one question. Are you saying that the date for that should be null in those records, or IF the date field in the form is null, then that is ok, and it can be any date?


Does that make any sense at all?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[sara82]

misscrf:

It's IF the date field in the form Is Null then it's ok.. so it would be ok if the user left the date field blank.

all I did was add this in the critera field

(Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) Or [forms]![frmWhatDates].[txtRecvStart]Is Null Or [forms]![frmWhatDates].[txtRecvEnd] Is Null

when i closed the query and opened it up again the query by itself added all this additional stuff i'm not sure why it took out the Is Null and put it in separate critera fields by iself and made the query what it looks above.