Dates larger or equal

  • Thread starter Thread starter Annelie
  • Start date Start date
A

Annelie

In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

Can there be a larger or equal to date? For example, if I want all of
October, I could put in 10/01/03 to 10/31/03 rather than he actual Week
Ending date. However, there are times when I need the print an individual
week, so I cannot asked just for the month.
Annelie
 
I'd recommend you stop using parameter queries and start using a form
to initiate your query.

You could have a combo or option group that lets you set the date
period to Day, Week, Month, or Year.

Then, you could enter the start date, have the AfterUpdate event of
the txtStartDate field compute the EndDate based on the period option
selected, then let the user update the end date if they want to.
Finally, use a command button to execute the query, where the query
criteria line looks something like:

Between NZ(Cdate(Forms!yourForm.txtStartDate),#11/02/01#)
And NZ(Cdate(Forms!yourForm.txtEndDate),#12/31/9999#)

By using the command button, you could also put some code in to test
that the date fields are valid, that the end date is greater than the
start date, etc.


--
HTH

Dale Fye


In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

Can there be a larger or equal to date? For example, if I want all of
October, I could put in 10/01/03 to 10/31/03 rather than he actual
Week
Ending date. However, there are times when I need the print an
individual
week, so I cannot asked just for the month.
Annelie
 
In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

Can there be a larger or equal to date? For example, if I want all of
October, I could put in 10/01/03 to 10/31/03 rather than he actual Week
Ending date. However, there are times when I need the print an individual
week, so I cannot asked just for the month.
Annelie
Click to expand...

Sure. One flexible way to do this is a criterion of

[datefield] >= [Enter start date] OR [Enter start date] IS NULL
AND
[datefield] <= [Enter end date] OR [Enter end date] IS NULL

This will prompt for a start and end date; if the user leaves the
start date blank they'll see all data older than the end date; if they
leave the end date blank they'll see all data newer than the start
date; if they leave both blank they'll see the whole table.
 
John, what a great and easy solution, works like a charm. I'll get around to
putting everything on the form when I can figure out how to combine a date
selection with a combo box for a name into one report.

John Vinson said:
In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

Can there be a larger or equal to date? For example, if I want all of
October, I could put in 10/01/03 to 10/31/03 rather than he actual Week
Ending date. However, there are times when I need the print an individual
week, so I cannot asked just for the month.
Annelie
Click to expand...

Sure. One flexible way to do this is a criterion of

[datefield] >= [Enter start date] OR [Enter start date] IS NULL
AND
[datefield] <= [Enter end date] OR [Enter end date] IS NULL

This will prompt for a start and end date; if the user leaves the
start date blank they'll see all data older than the end date; if they
leave the end date blank they'll see all data newer than the start
date; if they leave both blank they'll see the whole table.
Click to expand...
 
John, what a great and easy solution, works like a charm. I'll get around to
putting everything on the form when I can figure out how to combine a date
selection with a combo box for a name into one report.
Click to expand...

Just use
= Forms!nameofform!txtStartDate OR Forms!nameofform!txtStartDate IS NULL
Click to expand...

to refer to a textbox named txtStartDate instead of [Start Date] as a
criterion; same for enddate, and same again for the combo - just

=Forms!nameofform!cboMyCombo

Put a button on the Form to open the Report and you're in...!
 
There is a problem with the dates because they never clear out. I put the
statement in the query, was that correct?
Annelie


John Vinson said:
In my query if have the following criteria in the WEDate field:
Between NZ([Start Date],#11/02/01#) And NZ([End Date],#12/31/9999#)

Can there be a larger or equal to date? For example, if I want all of
October, I could put in 10/01/03 to 10/31/03 rather than he actual Week
Ending date. However, there are times when I need the print an individual
week, so I cannot asked just for the month.
Annelie
Click to expand...

Sure. One flexible way to do this is a criterion of

[datefield] >= [Enter start date] OR [Enter start date] IS NULL
AND
[datefield] <= [Enter end date] OR [Enter end date] IS NULL

This will prompt for a start and end date; if the user leaves the
start date blank they'll see all data older than the end date; if they
leave the end date blank they'll see all data newer than the start
date; if they leave both blank they'll see the whole table.
Click to expand...
 
I did not see your second reply when I wrote at about the query earlier. I
would prefer the form anyway, so I will try this in the morning.
Actually I am already using a combobox to select either one employee or one
job and then I let the query take care of the date. I created another combo
for the dates and either combo works fine, but I cannot get the report to
accept the dates and the name combined because I don't know how to add a
second code.

Private Sub Pirnt1_Click()
Dim strWhere As String
If IsNull(cboMoveTo) = False Then
strWhere = "[Jobno] = " & cboMoveTo
End If
DoCmd.OpenReport "YTDbyJobSEformat", acViewPreview, , strWhere
End Sub.

This is my on print event for the job number, how can I hang on the code for
the date
Annelie


John Vinson said:
John, what a great and easy solution, works like a charm. I'll get around to
putting everything on the form when I can figure out how to combine a date
selection with a combo box for a name into one report.
Click to expand...

Just use
= Forms!nameofform!txtStartDate OR Forms!nameofform!txtStartDate IS NULL
Click to expand...

to refer to a textbox named txtStartDate instead of [Start Date] as a
criterion; same for enddate, and same again for the combo - just

=Forms!nameofform!cboMyCombo

Put a button on the Form to open the Report and you're in...!
Click to expand...
 
I did not see your second reply when I wrote at about the query earlier. I
would prefer the form anyway, so I will try this in the morning.
Actually I am already using a combobox to select either one employee or one
job and then I let the query take care of the date. I created another combo
for the dates and either combo works fine, but I cannot get the report to
accept the dates and the name combined because I don't know how to add a
second code.

Private Sub Pirnt1_Click()
Dim strWhere As String
If IsNull(cboMoveTo) = False Then
strWhere = "[Jobno] = " & cboMoveTo
End If
DoCmd.OpenReport "YTDbyJobSEformat", acViewPreview, , strWhere
End Sub.

This is my on print event for the job number, how can I hang on the code for
the date
Annelie
Click to expand...

Unless you're using the Report from other than this button, I'd
suggest basing the Report on a Query which uses the form controls as
criteria, as suggested in my previous message; then you don't need a
strWhere at all, just open the Report and it will look to the form.

Alternatively, you can pass the date criteria in strWhere itself. This
will require some more complexity since you will need to construct a
valid SQL WHERE clause, with several criteria. Air code here,
untested:

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [datefield] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [datefield] <= #" & _
CDate(Me!txtDateTo) & "#"
End If

So if someone selects a number from the combo and types dates (in
whatever recognizable form, which CDate will convert to a valid date
value) strWhere will come out like

True AND [JobNo] = 345 AND [Datefield] >= #1/1/2003# AND [Datefield]
<= #10/1/2003#

I put the TRUE in there because a WHERE string starting with the word
AND would be erroneous.
 
I can't believe it, I got it to work. Thank you so much.
One problem, how can I get the DateFrom and DateTo into the header, is it
possible?
Trying to recreate this by employeeName for another report, I know I am
having a problem with quotation marks since it is a text field. Can you show
me on he below example, if JobNo were JobName, how should the line look.
Annelie

John Vinson said:
I did not see your second reply when I wrote at about the query earlier. I
would prefer the form anyway, so I will try this in the morning.
Actually I am already using a combobox to select either one employee or one
job and then I let the query take care of the date. I created another combo
for the dates and either combo works fine, but I cannot get the report to
accept the dates and the name combined because I don't know how to add a
second code.

Private Sub Pirnt1_Click()
Dim strWhere As String
If IsNull(cboMoveTo) = False Then
strWhere = "[Jobno] = " & cboMoveTo
End If
DoCmd.OpenReport "YTDbyJobSEformat", acViewPreview, , strWhere
End Sub.

This is my on print event for the job number, how can I hang on the code for
the date
Annelie
Click to expand...

Unless you're using the Report from other than this button, I'd
suggest basing the Report on a Query which uses the form controls as
criteria, as suggested in my previous message; then you don't need a
strWhere at all, just open the Report and it will look to the form.

Alternatively, you can pass the date criteria in strWhere itself. This
will require some more complexity since you will need to construct a
valid SQL WHERE clause, with several criteria. Air code here,
untested:

Dim strWhere As String
strWhere = "True" ' so records will be retrieved if no other
' criteria are entered
If Not IsNull(Me!cboMoveTo) Then
strWhere = strWhere & " AND [JobNo] = " & Me!cboMoveTo
End If
If Not IsNull(Me!txtDateFrom) Then
strWhere = strWhere & " AND [datefield] >= #" & _
CDate(Me!txtDateFrom) & "#"
End If
If Not IsNull(Me!txtDateTo) Then
strWhere = strWhere & " AND [datefield] <= #" & _
CDate(Me!txtDateTo) & "#"
End If

So if someone selects a number from the combo and types dates (in
whatever recognizable form, which CDate will convert to a valid date
value) strWhere will come out like

True AND [JobNo] = 345 AND [Datefield] >= #1/1/2003# AND [Datefield]
<= #10/1/2003#

I put the TRUE in there because a WHERE string starting with the word
AND would be erroneous.
Click to expand...
 
I can't believe it, I got it to work. Thank you so much.
One problem, how can I get the DateFrom and DateTo into the header, is it
possible?
Click to expand...

Sure. Just set the Control Source of the report header textboxes to

=Forms!yourformname!txtDateFrom

or the like.
Trying to recreate this by employeeName for another report, I know I am
having a problem with quotation marks since it is a text field. Can you show
me on he below example, if JobNo were JobName, how should the line look.
Annelie
Click to expand...

If Not IsNull(Me!cboJobName) Then
strWhere = strWhere & _
" AND [JobName] = " & Chr(34) & Me!cboMoveTo & Chr(34)
End If

Chr(34) is the doublequote character, so if JobName is Database Design
this would add the string

AND [JobName] = "Database Design"

to your strWhere.
 
John Vinson you are the best. I have it all working. I have been searching
for the answers for weeks.
Thank you so very much
Annelie


John Vinson said:
I can't believe it, I got it to work. Thank you so much.
One problem, how can I get the DateFrom and DateTo into the header, is it
possible?
Click to expand...

Sure. Just set the Control Source of the report header textboxes to

=Forms!yourformname!txtDateFrom

or the like.
Trying to recreate this by employeeName for another report, I know I am
having a problem with quotation marks since it is a text field. Can you show
me on he below example, if JobNo were JobName, how should the line look.
Annelie
Click to expand...

If Not IsNull(Me!cboJobName) Then
strWhere = strWhere & _
" AND [JobName] = " & Chr(34) & Me!cboMoveTo & Chr(34)
End If

Chr(34) is the doublequote character, so if JobName is Database Design
this would add the string

AND [JobName] = "Database Design"

to your strWhere.
Click to expand...
 
Back
Top