date useage in vb expression

[Microsoft]

I have an unbound combo box that uses the following code to choose a
particular type of information from a from. I have used it several times
for text fields, but now I need to use it for a date field. Can anyone help
me rewrite it for a date field?

DoCmd.ApplyFilter , "call_date = '" & Combo14 & "'"

Best regards,
Scott B

 

[tina]

try

DoCmd.ApplyFilter , "call_date = #" & Combo14 & "#"

hth

 

[Microsoft]

Tina,

I still get the same error, I get a debug dialog box and it highlights the
same line as before. Any thoughts?

Best regards,
Scott B

 

[tina]

post the complete text of the error you're getting.

 

[Ken Snell]

I believe you need to delete the comma after "Apply Filter".

DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"

 

[Microsoft]

thnak you Tina & Ken. The comma sems to be the trick to get the form to
open. But, it returns all the records. here is the code I have used inthe
text field forms.

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"

End Sub

Private Sub Form_Open(Cancel As Integer)
' Find the record that matches the control.
Dim rs As Object
DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"

End Sub

The combo box is unbound. The form is bound to a query that returns the
correct data. I currently have 6 records, all with different dates, so I
should only get one record per dae choice. But instead
I get all 8 records no matter what date I choose from the combo box. Can
this be done with a date?

Best regards
Scott B

 

[Ken Snell]

I am confused. Are you opening a new form based on the value that is in the
combo box? I don't see a DoCmd.OpenForm step in your code? But assuming that
you are using such a command, just use the filtering criterion as the fourth
argument of the DoCmd.OpenForm command:

DoCmd.OpenForm "Formname", , , "call_date = #" & Combo14 & "#"

No need for an ApplyFilter in the form's OnOpen event then.

 

[tina]

okay, now i get why you were putting that comma in the code originally. and
the syntax you used exactly matched the syntax in the Help topic, for using
a WHERE clause rather than a named filter or query.
i've assumed your scenario was this: you have a form bound to a query, and
when you open the form, all the records appear. you have an unbound combo
box on *that same form*, and when you choose a date from the droplist, you
want to filter the records *on that same open form* to see only the record
with the matching date.
if the above is correct, then according to the Help example, the filter
action - with that initial comma - should work. but since it doesn't, here's
a couple alternative codes to try:

DoCmd.ApplyFilter ,"[call_date] = #" & Me!Combo14 & "#"

or

DoCmd.ApplyFilter ,"[call_date] = #" & CDate(Me!Combo14) & "#"

or

Me.Filter = "[call_date] = #" & Me!Combo14 & "#"
'if the above line doesn't work, try the CDate(...) variation here, as well.
Me.FilterOn = True

if none of the code options above work, suggest you try using the CDate()
function on the date field in the combo box's RowSource. then test the code
options again.
btw, leave out the "Dim rs As Object" line altogether. if you posted the
entire code for the AfterUpdate event, then that line is doing nothing for
you at all - since rs is never used in the code.

hth

 

[Microsoft]

Zippity Do Dah that works great. Thanks to everyone who helped.

Best regards,
Scott B

okay, now i get why you were putting that comma in the code originally. and
the syntax you used exactly matched the syntax in the Help topic, for using
a WHERE clause rather than a named filter or query.
i've assumed your scenario was this: you have a form bound to a query, and
when you open the form, all the records appear. you have an unbound combo
box on *that same form*, and when you choose a date from the droplist, you
want to filter the records *on that same open form* to see only the record
with the matching date.
if the above is correct, then according to the Help example, the filter
action - with that initial comma - should work. but since it doesn't, here's
a couple alternative codes to try:

DoCmd.ApplyFilter ,"[call_date] = #" & Me!Combo14 & "#"

or

DoCmd.ApplyFilter ,"[call_date] = #" & CDate(Me!Combo14) & "#"

or

Me.Filter = "[call_date] = #" & Me!Combo14 & "#"
'if the above line doesn't work, try the CDate(...) variation here, as well.
Me.FilterOn = True

if none of the code options above work, suggest you try using the CDate()
function on the date field in the combo box's RowSource. then test the code
options again.
btw, leave out the "Dim rs As Object" line altogether. if you posted the
entire code for the AfterUpdate event, then that line is doing nothing for
you at all - since rs is never used in the code.

hth

thnak you Tina & Ken. The comma sems to be the trick to get the form to
open. But, it returns all the records. here is the code I have used inthe
text field forms.

Private Sub Combo14_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"

End Sub

Private Sub Form_Open(Cancel As Integer)
' Find the record that matches the control.
Dim rs As Object
DoCmd.ApplyFilter "call_date = #" & Combo14 & "#"

End Sub

The combo box is unbound. The form is bound to a query that returns the
correct data. I currently have 6 records, all with different dates, so I
should only get one record per dae choice. But instead
I get all 8 records no matter what date I choose from the combo box. Can
this be done with a date?

Best regards
Scott B

highlights
the

choose

a

 

[Microsoft]

One more question since you seem to understand what I am trying to do. My
next task is to create a form and report that will allow the user to type a
date or a range of dates into a dialog box (parameter query?) to filter the
records to show only those dates chosen. But (isn't there always a but) I
want to have the date(s) chosen appear on the form or report so that the
user will have a record of what they asked for. I know this can be done on
a report, but I cannot make it work in a form. I posted this on
access.forms, but so far I have not been able to make the information work
so I am trying here.

Thanks again for the help on the date problem.

Best regards,
Scott B

Zippity Do Dah that works great. Thanks to everyone who helped.

Best regards,
Scott B



and

snip

 

[tina]

one quick, easy solution that comes to mind is to create the form as
continuous form view (or single form view, if it suits your needs), and put
two unbound controls in the form header, as dtStart and dtEnd. write your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates will stay
there until 1) the user manually deletes them, or 2) the user types over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a report
showing the same records, those two unbound controls can do double duty as
references to filter the report records also.

hth

 

[Microsoft]

I must be doing something wrong. I put the unbound fields in the header and
called them dtStart and dtEnd. Then I referenced them in the query criteria
line for the call_date field. When I open the query it opens a dialog box
and asks for the dates in a dialog box that references the form then and
gives me data based on the dates I entered, but when I open the form, it
just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is the SQL for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ", " &
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received], [tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID]) ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];

 

[tina]

you're right, that solution was a bit flawed. sorry!
if you want the form to open "already filtered", you'll need to create a
small unbound form to hold the unbound date controls. in the query,
reference the date controls on the unbound form. use a command button on
that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be filtered by
dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command button next
to those unbound controls in the form header, and use it to filter the form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth

I must be doing something wrong. I put the unbound fields in the header and
called them dtStart and dtEnd. Then I referenced them in the query criteria
line for the call_date field. When I open the query it opens a dialog box
and asks for the dates in a dialog box that references the form then and
gives me data based on the dates I entered, but when I open the form, it
just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is the SQL for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ", " &
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received], [tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID]) ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];

one quick, easy solution that comes to mind is to create the form as
continuous form view (or single form view, if it suits your needs), and put
two unbound controls in the form header, as dtStart and dtEnd. write your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates will stay
there until 1) the user manually deletes them, or 2) the user types over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a report
showing the same records, those two unbound controls can do double duty as
references to filter the report records also.

hth


type filter
the

but)

I done
on

 

[Scott B]

I am having one problem. The unbound form opens and accepts the dates and I
get no error messages, but the query still pops up the paramter dialog box
after I enter the dates in the unbound form. It does gove mne the data i
sak for. But why do I have to enter the dates twice to get it to work?
Also, the real reason for all this aggrevation is that I need to have the
parameter dates from the query appear on the form as they do on the report.
I am really surprised that this particular action is so difficult for forms
and simple for reports when usually whatever you do in reports you can do on
a form.

Best regards,
Scott B

you're right, that solution was a bit flawed. sorry!
if you want the form to open "already filtered", you'll need to create a
small unbound form to hold the unbound date controls. in the query,
reference the date controls on the unbound form. use a command button on
that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be filtered by
dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command button next
to those unbound controls in the form header, and use it to filter the form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth

I must be doing something wrong. I put the unbound fields in the header and
called them dtStart and dtEnd. Then I referenced them in the query criteria
line for the call_date field. When I open the query it opens a dialog box
and asks for the dates in a dialog box that references the form then and
gives me data based on the dates I entered, but when I open the form, it
just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is the SQL for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ", " &
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received], [tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID]) ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];

one quick, easy solution that comes to mind is to create the form as
continuous form view (or single form view, if it suits your needs),

and
put

two unbound controls in the form header, as dtStart and dtEnd. write your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates will stay
there until 1) the user manually deletes them, or 2) the user types over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a report
showing the same records, those two unbound controls can do double

duty

as do.
My but) information
work

 

[tina]

if you're still getting the parameter dialog box, then you didn't remove the
criteria from the query as i instructed. fix that, and try it again.

there are a number of ways to show the "entered" dates on the bound form.
the simplest is to keep the unbound form open - you can make it invisible if
you need to. put two unbound textbox controls on the header or footer of the
bound form and set the ControlSource to
= Forms!MyUnboundForm!dtStart
on one, and
= Forms!MyUnboundForm!dtEnd
on the other.
btw, you can also reference those values the same way in the report, as long
as the unbound form remains open.

hth

I am having one problem. The unbound form opens and accepts the dates and I
get no error messages, but the query still pops up the paramter dialog box
after I enter the dates in the unbound form. It does gove mne the data i
sak for. But why do I have to enter the dates twice to get it to work?
Also, the real reason for all this aggrevation is that I need to have the
parameter dates from the query appear on the form as they do on the report.
I am really surprised that this particular action is so difficult for forms
and simple for reports when usually whatever you do in reports you can do on
a form.

Best regards,
Scott B

you're right, that solution was a bit flawed. sorry!
if you want the form to open "already filtered", you'll need to create a
small unbound form to hold the unbound date controls. in the query,
reference the date controls on the unbound form. use a command button on
that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be filtered by
dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command button next
to those unbound controls in the form header, and use it to filter the form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth

I must be doing something wrong. I put the unbound fields in the

header
and

called them dtStart and dtEnd. Then I referenced them in the query criteria
line for the call_date field. When I open the query it opens a dialog box
and asks for the dates in a dialog box that references the form then and
gives me data based on the dates I entered, but when I open the form, it
just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is the

SQL
for

the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ", " &
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received], [tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID]) ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];



one quick, easy solution that comes to mind is to create the form as
continuous form view (or single form view, if it suits your needs), and
put
two unbound controls in the form header, as dtStart and dtEnd. write your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates will stay
there until 1) the user manually deletes them, or 2) the user types over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a report
showing the same records, those two unbound controls can do double

duty

as
references to filter the report records also.

hth


One more question since you seem to understand what I am trying to do.
My
next task is to create a form and report that will allow the user to
type
a
date or a range of dates into a dialog box (parameter query?) to filter
the
records to show only those dates chosen. But (isn't there always

a
but)

I
want to have the date(s) chosen appear on the form or report so

that
the

user will have a record of what they asked for. I know this can

be
done

on
a report, but I cannot make it work in a form. I posted this on
access.forms, but so far I have not been able to make the information
work
so I am trying here.

Thanks again for the help on the date problem.

Best regards,
Scott B


Zippity Do Dah that works great. Thanks to everyone who helped.

Best regards,
Scott B


okay, now i get why you were putting that comma in the code
originally.
and
snip

 

[Scott B]

Are you saying there would be no criteria in the query? If I take it out,
then I will just get a list of calls for all dates. Here is the criteria in
the query I have right now:

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

I feel confused. My understanding of a parameter query is that it is a
select query with parameter(s) in the criteria line. Whenever you open the
query or anything that uses the query as it's data source, the query will
prompt for whatever you have in the criteria line and return the data
matching that criteria, in my case a range of dates. Now, the form I am
using uses the query as the data source with the parameters in the criteria
line. This works fine. But, I want to see the dates the query is using on
my form. I understand you to say that I need to have a seperate unbound
form with two unbound controls whose name is in the the query criteria line.
The query criteria line uses the unbound control names instead of the usual
"[Type begining date here:], etc. But what I see is that the unbound forms
opens and asks for the dates and then the query parameter doialig box opens
and asks again (see criteria line above). And the unbound text boxes in the
header of the form gives me a #name error.

I am sorry to be so dense, but I cannot seem to get this right. I have
learned how to do many things from these Access new groups. In fact most of
what I know about VBA and events and advanced stuff is from here. But this
particular problem has been a real bear. Thank you for you time and I will
understand if you decide to end this conversation.

Best regards,
Scott B

if you're still getting the parameter dialog box, then you didn't remove the
criteria from the query as i instructed. fix that, and try it again.

there are a number of ways to show the "entered" dates on the bound form.
the simplest is to keep the unbound form open - you can make it invisible if
you need to. put two unbound textbox controls on the header or footer of the
bound form and set the ControlSource to
= Forms!MyUnboundForm!dtStart
on one, and
= Forms!MyUnboundForm!dtEnd
on the other.
btw, you can also reference those values the same way in the report, as long
as the unbound form remains open.

hth

I am having one problem. The unbound form opens and accepts the dates

and

I
get no error messages, but the query still pops up the paramter dialog box
after I enter the dates in the unbound form. It does gove mne the data i
sak for. But why do I have to enter the dates twice to get it to work?
Also, the real reason for all this aggrevation is that I need to have the
parameter dates from the query appear on the form as they do on the report.
I am really surprised that this particular action is so difficult for forms
and simple for reports when usually whatever you do in reports you can

do

on
a form.

Best regards,
Scott B

filtered

by

dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command

button
next

to those unbound controls in the form header, and use it to filter the form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth


I must be doing something wrong. I put the unbound fields in the header
and
called them dtStart and dtEnd. Then I referenced them in the query
criteria
line for the call_date field. When I open the query it opens a

dialog
box

and asks for the dates in a dialog box that references the form then and
gives me data based on the dates I entered, but when I open the

form,

it

just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is the SQL
for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ", " &
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received],
[tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames RIGHT
JOIN tblRunInfo ON

[tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID])
ON

[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];



one quick, easy solution that comes to mind is to create the form as
continuous form view (or single form view, if it suits your

needs),
and

put
two unbound controls in the form header, as dtStart and dtEnd. write
your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates

will
stay

there until 1) the user manually deletes them, or 2) the user

types
over

them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a report
showing the same records, those two unbound controls can do double duty
as
references to filter the report records also.

hth


One more question since you seem to understand what I am trying

to
do.

My
next task is to create a form and report that will allow the

user

to

always

a

 

[tina]

well, what you want to do really isn't that difficult, and there are several
ways to choose to do it - we're just having major communication problems.
you're not dense. i gave you two possible solutions - then when you had a
problem, i gave you a correction based on the wrong solution. i'm really off
my game with this thread, for some reason. but if you want to hang in there,
so will i.

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

if the above is the *exact* criteria you have in your query, then your
reference may be the problem. change it to:

Between [Forms]![frmChooseDate]![ChooseBeginDate] And
[Forms]![frmChooseDate]![ChooseEndDate]

And the unbound text boxes in the
header of the form gives me a #name error.

check the ControlSource of those text boxes to see if you need to correct
the reference there, as well.

hth

Are you saying there would be no criteria in the query? If I take it out,
then I will just get a list of calls for all dates. Here is the criteria in
the query I have right now:

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

I feel confused. My understanding of a parameter query is that it is a
select query with parameter(s) in the criteria line. Whenever you open the
query or anything that uses the query as it's data source, the query will
prompt for whatever you have in the criteria line and return the data
matching that criteria, in my case a range of dates. Now, the form I am
using uses the query as the data source with the parameters in the criteria
line. This works fine. But, I want to see the dates the query is using on
my form. I understand you to say that I need to have a seperate unbound
form with two unbound controls whose name is in the the query criteria line.
The query criteria line uses the unbound control names instead of the usual
"[Type begining date here:], etc. But what I see is that the unbound forms
opens and asks for the dates and then the query parameter doialig box opens
and asks again (see criteria line above). And the unbound text boxes in the
header of the form gives me a #name error.

I am sorry to be so dense, but I cannot seem to get this right. I have
learned how to do many things from these Access new groups. In fact most of
what I know about VBA and events and advanced stuff is from here. But this
particular problem has been a real bear. Thank you for you time and I will
understand if you decide to end this conversation.

Best regards,
Scott B

if you're still getting the parameter dialog box, then you didn't remove the
criteria from the query as i instructed. fix that, and try it again.

there are a number of ways to show the "entered" dates on the bound form.
the simplest is to keep the unbound form open - you can make it

invisible

if
you need to. put two unbound textbox controls on the header or footer of the
bound form and set the ControlSource to
= Forms!MyUnboundForm!dtStart
on one, and
= Forms!MyUnboundForm!dtEnd
on the other.
btw, you can also reference those values the same way in the report, as long
as the unbound form remains open.

hth


and

data

i do

create

a

button

on

that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be

filtered

by
dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command button
next
to those unbound controls in the form header, and use it to filter the
form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth


I must be doing something wrong. I put the unbound fields in the header
and
called them dtStart and dtEnd. Then I referenced them in the query
criteria
line for the call_date field. When I open the query it opens a dialog
box
and asks for the dates in a dialog box that references the form

then
and

gives me data based on the dates I entered, but when I open the

form,

it
just opens with no data and no dates in the unbound text boxes in the
header. The form uses the query as it's data source. Below is

the
SQL

for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+". "+[last_name1]) AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] & ",

"

&

[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received],
[tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames
RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID])
ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];



one quick, easy solution that comes to mind is to create the

form

as

trying

to user always so
that can
be

 

[Scott B]

Halleluia! I works like a charm. I shouldn't do this at night when I'm
tired. I reread your instructions this morning and checked all my spelling
and lo and behold it works.

I cannot thank you enough for all your help and patience.

Best regards,
Scott B

well, what you want to do really isn't that difficult, and there are several
ways to choose to do it - we're just having major communication problems.
you're not dense. i gave you two possible solutions - then when you had a
problem, i gave you a correction based on the wrong solution. i'm really off
my game with this thread, for some reason. but if you want to hang in there,
so will i.

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

if the above is the *exact* criteria you have in your query, then your
reference may be the problem. change it to:

Between [Forms]![frmChooseDate]![ChooseBeginDate] And
[Forms]![frmChooseDate]![ChooseEndDate]

And the unbound text boxes in the
header of the form gives me a #name error.

check the ControlSource of those text boxes to see if you need to correct
the reference there, as well.

hth

Are you saying there would be no criteria in the query? If I take it out,
then I will just get a list of calls for all dates. Here is the

criteria

in
the query I have right now:

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

I feel confused. My understanding of a parameter query is that it is a
select query with parameter(s) in the criteria line. Whenever you open the
query or anything that uses the query as it's data source, the query will
prompt for whatever you have in the criteria line and return the data
matching that criteria, in my case a range of dates. Now, the form I am
using uses the query as the data source with the parameters in the criteria
line. This works fine. But, I want to see the dates the query is using on
my form. I understand you to say that I need to have a seperate unbound
form with two unbound controls whose name is in the the query criteria line.
The query criteria line uses the unbound control names instead of the usual
"[Type begining date here:], etc. But what I see is that the unbound forms
opens and asks for the dates and then the query parameter doialig box opens
and asks again (see criteria line above). And the unbound text boxes in the
header of the form gives me a #name error.

I am sorry to be so dense, but I cannot seem to get this right. I have
learned how to do many things from these Access new groups. In fact

most

of
what I know about VBA and events and advanced stuff is from here. But this
particular problem has been a real bear. Thank you for you time and I will
understand if you decide to end this conversation.

Best regards,
Scott B

if you're still getting the parameter dialog box, then you didn't

remove
the

criteria from the query as i instructed. fix that, and try it again.

there are a number of ways to show the "entered" dates on the bound form.
the simplest is to keep the unbound form open - you can make it

invisible

if
you need to. put two unbound textbox controls on the header or footer

of
the

bound form and set the ControlSource to
= Forms!MyUnboundForm!dtStart
on one, and
= Forms!MyUnboundForm!dtEnd
on the other.
btw, you can also reference those values the same way in the report,

as
long

as the unbound form remains open.

hth


I am having one problem. The unbound form opens and accepts the

dates
and

I
get no error messages, but the query still pops up the paramter

dialog
box

after I enter the dates in the unbound form. It does gove mne the

data

i
sak for. But why do I have to enter the dates twice to get it to work?
Also, the real reason for all this aggrevation is that I need to

have
the

parameter dates from the query appear on the form as they do on the
report.
I am really surprised that this particular action is so difficult for
forms
and simple for reports when usually whatever you do in reports you

can
do

on
a form.

Best regards,
Scott B

you're right, that solution was a bit flawed. sorry!
if you want the form to open "already filtered", you'll need to

create

a
small unbound form to hold the unbound date controls. in the query,
reference the date controls on the unbound form. use a command

button

on
that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be filtered
by
dates the user enters, then set the form up as i told you in my previous
post. *remove the criteria in the query.* instead, put a command button
next
to those unbound controls in the form header, and use it to filter the
form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth


I must be doing something wrong. I put the unbound fields in the
header
and
called them dtStart and dtEnd. Then I referenced them in the query
criteria
line for the call_date field. When I open the query it opens a dialog
box
and asks for the dates in a dialog box that references the form then
and
gives me data based on the dates I entered, but when I open the form,
it
just opens with no data and no dates in the unbound text boxes

in
the

header. The form uses the query as it's data source. Below is the
SQL
for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+". " &
[last_name1] & " and "+[first_name2]+" "+[mi1]+".

"+[last_name1])

AS

[Names], ([address1] & " " & [address2]) AS Address, ([city] &

",

"

&
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received],
[tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames
RIGHT
JOIN tblRunInfo ON [tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID])
ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];



one quick, easy solution that comes to mind is to create the

form

as
continuous form view (or single form view, if it suits your needs),
and
put
two unbound controls in the form header, as dtStart and dtEnd. write
your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates will
stay
there until 1) the user manually deletes them, or 2) the user types
over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to run a
report
showing the same records, those two unbound controls can do double
duty
as
references to filter the report records also.

hth


One more question since you seem to understand what I am

trying

to
do.
My
next task is to create a form and report that will allow the user
to
type
a
date or a range of dates into a dialog box (parameter

query?)

to

 

[tina]

you're welcome, glad it works - and thanks for your patience as well!
btw, when you're ready to go a step further in coding - there are ways to
accomplish the same thing (including addressing the report needs) in VBA
without using the extra form.

Halleluia! I works like a charm. I shouldn't do this at night when I'm
tired. I reread your instructions this morning and checked all my spelling
and lo and behold it works.

I cannot thank you enough for all your help and patience.

Best regards,
Scott B

well, what you want to do really isn't that difficult, and there are several
ways to choose to do it - we're just having major communication problems.
you're not dense. i gave you two possible solutions - then when you had a
problem, i gave you a correction based on the wrong solution. i'm really off
my game with this thread, for some reason. but if you want to hang in there,
so will i.

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

if the above is the *exact* criteria you have in your query, then your
reference may be the problem. change it to:

Between [Forms]![frmChooseDate]![ChooseBeginDate] And
[Forms]![frmChooseDate]![ChooseEndDate]

And the unbound text boxes in the
header of the form gives me a #name error.

check the ControlSource of those text boxes to see if you need to correct
the reference there, as well.

hth

Are you saying there would be no criteria in the query? If I take it out,
then I will just get a list of calls for all dates. Here is the

criteria

in
the query I have right now:

Between [Form]![frmChooseDate]![ChooseBeginDate] And
[Form]![frmChooseDate]![ChooseEndDate]

I feel confused. My understanding of a parameter query is that it is a
select query with parameter(s) in the criteria line. Whenever you

open
the

query or anything that uses the query as it's data source, the query will
prompt for whatever you have in the criteria line and return the data
matching that criteria, in my case a range of dates. Now, the form I am
using uses the query as the data source with the parameters in the criteria
line. This works fine. But, I want to see the dates the query is

using
on

my form. I understand you to say that I need to have a seperate unbound
form with two unbound controls whose name is in the the query criteria line.
The query criteria line uses the unbound control names instead of the usual
"[Type begining date here:], etc. But what I see is that the unbound forms
opens and asks for the dates and then the query parameter doialig box opens
and asks again (see criteria line above). And the unbound text boxes

in
the

header of the form gives me a #name error.

I am sorry to be so dense, but I cannot seem to get this right. I have
learned how to do many things from these Access new groups. In fact

most

of
what I know about VBA and events and advanced stuff is from here. But this
particular problem has been a real bear. Thank you for you time and I will
understand if you decide to end this conversation.

Best regards,
Scott B

if you're still getting the parameter dialog box, then you didn't remove
the
criteria from the query as i instructed. fix that, and try it again.

there are a number of ways to show the "entered" dates on the bound form.
the simplest is to keep the unbound form open - you can make it invisible
if
you need to. put two unbound textbox controls on the header or

footer

of

the
bound form and set the ControlSource to
= Forms!MyUnboundForm!dtStart
on one, and
= Forms!MyUnboundForm!dtEnd
on the other.
btw, you can also reference those values the same way in the report, as
long
as the unbound form remains open.

hth


I am having one problem. The unbound form opens and accepts the dates
and
I
get no error messages, but the query still pops up the paramter dialog
box
after I enter the dates in the unbound form. It does gove mne the data
i
sak for. But why do I have to enter the dates twice to get it to work?
Also, the real reason for all this aggrevation is that I need to have
the
parameter dates from the query appear on the form as they do on the
report.
I am really surprised that this particular action is so difficult for
forms
and simple for reports when usually whatever you do in reports you can
do
on
a form.

Best regards,
Scott B

you're right, that solution was a bit flawed. sorry!
if you want the form to open "already filtered", you'll need to create
a
small unbound form to hold the unbound date controls. in the query,
reference the date controls on the unbound form. use a command button
on
that form to open the "real" form, once the dates have been entered.
if you want the form to open with all the records, and then be
filtered
by
dates the user enters, then set the form up as i told you in my
previous
post. *remove the criteria in the query.* instead, put a command
button
next
to those unbound controls in the form header, and use it to

filter
the

form
after dates have been entered, as

Private Sub CommandButtonName_Click()

With Me
If IsNull(.dtStart ) Or IsNull(.dtEnd) Then
Msgbox "Enter both dates, please."
.dtStart.SetFocus
Else
.Filter = "[call_date] Between #" & .dtStart _
& "# And " & .dtEnd & "#"
.FilterOn = True
End With

End Sub

hth


I must be doing something wrong. I put the unbound fields in the
header
and
called them dtStart and dtEnd. Then I referenced them in the query
criteria
line for the call_date field. When I open the query it opens a
dialog
box
and asks for the dates in a dialog box that references the

form
then

and
gives me data based on the dates I entered, but when I open the
form,
it
just opens with no data and no dates in the unbound text boxes in
the
header. The form uses the query as it's data source. Below

is
the

SQL
for
the query. Any thoughts?

Best regards,
Scott B

SELECT [tblRunInfo].[run_number], [tblRunInfo].[call_date],
[tblRunInfo].[call_time], [tblCallNames].[last_name1],
[tblCallType].[call_type], [tblRunInfo].[call_type_ID],
[tblRunInfo].[call_location], ([first_name1] & " " & [mi1]+".

"

&

[last_name1] & " and "+[first_name2]+" "+[mi1]+".

"+[last_name1])

AS
[Names], ([address1] & " " & [address2]) AS Address, ([city] &

",

"
&
[state] & " " & [zip]) AS CityStateZip, [tblRunInfo].[run_sheet],
[tblRunInfo].[bfir], [tblRunInfo].[pcr], [tblRunInfo].[ekg],
[tblRunInfo].[misc], [tblRunInfo].[mutual_aid_given],
[tblRunInfo].[mutual_aid_received],
[tblDepartments].[mutual_aid_dept_name]
FROM tblDepartments RIGHT JOIN (tblCallType RIGHT JOIN (tblCallNames
RIGHT
JOIN tblRunInfo ON
[tblCallNames].[ID]=[tblRunInfo].[tblCallNames_ID])
ON
[tblCallType].[call_type_ID]=[tblRunInfo].[call_type_ID]) ON
[tblDepartments].[ID]=[tblRunInfo].[mutual_aid_dept_id]
WHERE ((([tblRunInfo].[call_date]) Between
[Forms]![frmCallsByDateChooseDate]![dtStart] And
[Forms]![frmCallsByDateChooseDate]![dtEnd]))
ORDER BY [tblRunInfo].[call_date], [tblRunInfo].[call_time],
[tblCallType].[call_type];



one quick, easy solution that comes to mind is to create the form
as
continuous form view (or single form view, if it suits your
needs),
and
put
two unbound controls in the form header, as dtStart and dtEnd.
write
your
query or filter to reference those two controls.
unless you write code to clear the controls, the entered dates
will
stay
there until 1) the user manually deletes them, or 2) the user
types
over
them, or 3) the form is closed.
if you're filtering records in the form, and then want to

run

a query?) report
so this
can this
on