running a query & displaying a report

[Nick T]

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Wayne-I-M]

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH

 

[Nick T]

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Nick T]

Hi,

Think i know why this isnt working, but dont know how to resolve. My querys
date field is date & time ie. 07/08/2009 10:23:08, therefore when i set the
criteria to Forms!MainMenu!Date and just type 07/08/2009 into my 'Date' text
box, nothing is displayed.

On the basis that i cant change my query data, can i change somthing in the
criteria to make it just look for the dd/mm/yyyy instead of everything in the
field??

Help greatly appreciated.

Thanks

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Wayne-I-M]

Hi

1st thing to do is to change your form control's name to
txtDate

so your query criteria should be
Forms!MainMenu!txtDate
(I assume that this is not a subform ??)

Next change the query column format to
Short Date

and this should work fine

--
Wayne
Manchester, England.

Hi,

Think i know why this isnt working, but dont know how to resolve. My querys
date field is date & time ie. 07/08/2009 10:23:08, therefore when i set the
criteria to Forms!MainMenu!Date and just type 07/08/2009 into my 'Date' text
box, nothing is displayed.

On the basis that i cant change my query data, can i change somthing in the
criteria to make it just look for the dd/mm/yyyy instead of everything in the
field??

Help greatly appreciated.

Thanks

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.



:

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Nick T]

Hiya,
Still no luck with this!
It doesnt seem to be liking it. Im not getting any errors, however my
report isnt displaying any data. (which is available).

Is there anything i can add to my query criteria which says only look at the
dd/mm/yyyy as opposed all of the data in that field??

I have changed the format to 'Short date' and only dd/mm/yyyy is displayed,
however still no luck......

Any other suggestions, or am i missing somthing??
Thanks

Hi

1st thing to do is to change your form control's name to
txtDate

so your query criteria should be
Forms!MainMenu!txtDate
(I assume that this is not a subform ??)

Next change the query column format to
Short Date

and this should work fine

--
Wayne
Manchester, England.

Hi,

Think i know why this isnt working, but dont know how to resolve. My querys
date field is date & time ie. 07/08/2009 10:23:08, therefore when i set the
criteria to Forms!MainMenu!Date and just type 07/08/2009 into my 'Date' text
box, nothing is displayed.

On the basis that i cant change my query data, can i change somthing in the
criteria to make it just look for the dd/mm/yyyy instead of everything in the
field??

Help greatly appreciated.

Thanks

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks



:

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.



:

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Wayne-I-M]

Hi Nick

It really shouldn't make any difference.

If you have a date/time field and you only send the date then the time is
fillin (behind the application) with 00:00.

So you need to look at how you are sending the criteria

Are you getting any error messages - if yes, post them here.

Only is the path (criteria right)
Right click the criteria row
select build
Select all forms
Navigate to the form and paste the control

etc
etc

--
Wayne
Manchester, England.

Hiya,
Still no luck with this!
It doesnt seem to be liking it. Im not getting any errors, however my
report isnt displaying any data. (which is available).

Is there anything i can add to my query criteria which says only look at the
dd/mm/yyyy as opposed all of the data in that field??

I have changed the format to 'Short date' and only dd/mm/yyyy is displayed,
however still no luck......

Any other suggestions, or am i missing somthing??
Thanks

Hi

1st thing to do is to change your form control's name to
txtDate

so your query criteria should be
Forms!MainMenu!txtDate
(I assume that this is not a subform ??)

Next change the query column format to
Short Date

and this should work fine

--
Wayne
Manchester, England.

Hi,

Think i know why this isnt working, but dont know how to resolve. My querys
date field is date & time ie. 07/08/2009 10:23:08, therefore when i set the
criteria to Forms!MainMenu!Date and just type 07/08/2009 into my 'Date' text
box, nothing is displayed.

On the basis that i cant change my query data, can i change somthing in the
criteria to make it just look for the dd/mm/yyyy instead of everything in the
field??

Help greatly appreciated.

Thanks



:

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks



:

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.



:

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[John Spencer]

Changing the format to short date does not change the data in the field.

Simple way
-- Add another field to your query
DateValue([YourDateField]
-- Set the criteria under it to
CDate(Forms!MainMenu!txtDate)

A better way, change the criteria under your field to

=CDate(Forms!MainMenu!txtDate) AND <DateAdd("d",1,CDate(Forms!MainMenu!txtDate))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Nick T]

Thanks for posting back - appreciate the help.

Im not getting any error messages.
My query gets its data from a table. The info is date/time format in the
table ie. 05/05/2009 07:49:38 however on my query i have set the fomat to
'Short time' and therefore the info is displayed as just 05/05/2009. However
if i type this date in my text box on my form, my report displays with no
info showing. Although i know the theory works, because if i type all of the
05/05/2009 07:49:38 into my text box, then my report does show this record.

The problem is, is that i have lots of records (from different times) on the
same day that i want my report to show, hense why i need to search just the
date & not the time as well.

Does this little extra info lead to any more suggestions??
Greatly appreciated,

Hi Nick

It really shouldn't make any difference.

If you have a date/time field and you only send the date then the time is
fillin (behind the application) with 00:00.

So you need to look at how you are sending the criteria

Are you getting any error messages - if yes, post them here.

Only is the path (criteria right)
Right click the criteria row
select build
Select all forms
Navigate to the form and paste the control

etc
etc

--
Wayne
Manchester, England.

Hiya,
Still no luck with this!
It doesnt seem to be liking it. Im not getting any errors, however my
report isnt displaying any data. (which is available).

Is there anything i can add to my query criteria which says only look at the
dd/mm/yyyy as opposed all of the data in that field??

I have changed the format to 'Short date' and only dd/mm/yyyy is displayed,
however still no luck......

Any other suggestions, or am i missing somthing??
Thanks

Hi

1st thing to do is to change your form control's name to
txtDate

so your query criteria should be
Forms!MainMenu!txtDate
(I assume that this is not a subform ??)

Next change the query column format to
Short Date

and this should work fine

--
Wayne
Manchester, England.



:

Hi,

Think i know why this isnt working, but dont know how to resolve. My querys
date field is date & time ie. 07/08/2009 10:23:08, therefore when i set the
criteria to Forms!MainMenu!Date and just type 07/08/2009 into my 'Date' text
box, nothing is displayed.

On the basis that i cant change my query data, can i change somthing in the
criteria to make it just look for the dd/mm/yyyy instead of everything in the
field??

Help greatly appreciated.

Thanks



:

Hi Wayne,
Thanks for the suggestions, but cant seem to get this working(?).
Iv tried adding to Forms!MainMenu!Date to the criteria of my query, but this
doesnt seem to work??

Any other suggestions, or anything i can tell you about my db which will
help solve this??
Many thanks



:

Hi

Add this to criteria row of the query that the report is based on (the date
column)
Forms![FormName]![ControlName]
Then when you open the report it will just show the date you have put in.

You could have the OnClick of your button something like this

Private Sub ButtonName_Click()
If Not IsNull(Me.NameOfTextBoxOnFormWithDate) Then
DoCmd.OpenReport "NameOfReport", acViewPreview, "", "", acNormal
Else
MsgBox "You must add a date to the box before you can open the
report" & Chr(13) & "Go back to the box and enter a date", vbInformation, "No
Date Found"
Me.NameOfTextBoxOnFormWithDate.SetFocus
End If
End Sub


HtH



--
Wayne
Manchester, England.



:

Hi,
I have a db which has a form. On my form is a text box into which i will
type a date ie 07/08/2009. Then i have a cmd button which opens a report.
The report gets its data from a query.

What i want to do is when my cmd button is clicked, the report that is
displayed, displays the info from the date for which i have typed into my
text box on my form.

Any suggestions greatly appreciated.

Many thanks

P.S - in beginners lingo please.!!

 

[Nick T]

Excellent - many thanks.....

the 'better way' works just fine.

Thanks both for your assistance.

Changing the format to short date does not change the data in the field.

Simple way
-- Add another field to your query
DateValue([YourDateField]
-- Set the criteria under it to
CDate(Forms!MainMenu!txtDate)

A better way, change the criteria under your field to

=CDate(Forms!MainMenu!txtDate) AND <DateAdd("d",1,CDate(Forms!MainMenu!txtDate))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi

1st thing to do is to change your form control's name to
txtDate

so your query criteria should be
Forms!MainMenu!txtDate
(I assume that this is not a subform ??)

Next change the query column format to
Short Date

and this should work fine