By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

user input to query

P: n/a
Hi,

I have a report that is a based on a query.
One of the fields in the query is "time of sale", which is a date
field that holds the sales dates (day,month,year).
I want to use that field as a month criteria in the query, so the report
will print only records that holds a specific month in that field.
How can I prompt the user to enter the month and pass it to the query ?

Thanks!
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
In message <ne********************@lnews.actcom.co.il>, Marius Kaizerman
<ma****@kaizerman.org> writes
Hi,

I have a report that is a based on a query.
One of the fields in the query is "time of sale", which is a date
field that holds the sales dates (day,month,year).
I want to use that field as a month criteria in the query, so the report
will print only records that holds a specific month in that field.
How can I prompt the user to enter the month and pass it to the query ?


The simple way is to include an extra field in the query. If you call it
[Sales Month] then Access will prompt for a value whenever the query is
executed. If you trust your users to enter the right data every time
then this is enough. More realistically you need to put an extra field
on a form and have the users enter the month into it. You may be able to
give it a sensible default value if, for instance, users usually run
reports for the previous month.

You should add some code to make sure that when the user enters a value
they enter one that makes sense.

Code like this may do what you want: usual disclaimers apply.

----------------------8<-------------------------------------8<----------
-------------------------

Private Sub PrintThis_Click()
On Error GoTo Err_PrintThis_Click
RunCommand acCmdSaveRecord
Dim stDocName, WhereString As String
WhereString = "History.ID = " & Me.ID

stDocName = "Single Linked History Detail"
DoCmd.OpenReport stDocName, acNormal, "History", WhereString

Exit_PrintThis_Click:
Exit Sub

Err_PrintThis_Click:
MsgBox Err.Description
Resume Exit_PrintThis_Click
End Sub

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 12 '05 #2

P: n/a
1) Insert in criteria for that field something like:
[What's the month ? ] as a data-entry point
2) I suppose <time of sales> is an alias for something like <timesales>
that's the real name of your field
Replace in the query your field with: Times of sales:
Int(Format([timesales];"mm")) that filters it only for month.

Bye, Marco.

"Marius Kaizerman" <ma****@kaizerman.org> ha scritto nel messaggio
news:ne********************@lnews.actcom.co.il...
Hi,

I have a report that is a based on a query.
One of the fields in the query is "time of sale", which is a date
field that holds the sales dates (day,month,year).
I want to use that field as a month criteria in the query, so the report
will print only records that holds a specific month in that field.
How can I prompt the user to enter the month and pass it to the query ?

Thanks!

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.