inputting criteria from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

i am creating a query that i need to ask the user for a start date and a end
date, how do i create a form with a calendar control that passes the date
back to the query? i know how to make a form having the calender control on
it, but i dont know how to call that calender from the query. how do i get
the criteria row to call the form?

additionally how do i transfer the date selected from a calendar control to
a field or to the criteria? i love the calendar date control but i have never
really succeded in utilizing it.
 
create your form with the calendar controls for the StartDate and EndDate.
you can add a command button to the form, so the user can run the query (or
the report based on the query, whatever) by clicking the button after
choosing the start and end dates. in the query, set criteria on the date
field, as

between Forms!FormName!txtStartDate And Forms!FormName!txtEndDate

substitute the correct name of the form, and the names of the controls you
created to hold the chosen dates.

hth
 
does that mean i need 2 different forms or could i use 2 controls one that
updates from a calendar control to be the start date, and another that
updates to be the end date and then pass those off to the query?

what i am thinking is have 2 text boxes, one called start date, the other
end date. then have a calander control where they can choose the date, push a
button called start date, then choose another date in the calander control
and hit a button called end date, then hit a button called submit and it
would send that information to the query/report.

is it possible? will it work? is there an example of something similar out
there?
 
you can use one form. your scenario of choosing a date and then clicking a
button to "fill in" either the Start or End textbox should work fine. it
would be less work for your users, though, to simply have two calendars on
the form, one labeled "Start Date" and one labeled "End Date". the user
could simply click on a date in each calendar, then click a command button
to run the query.

as for examples, there's probably some on the web someplace, but i couldn't
tell you where to look. i haven't used a calendar control in several years
(from a data entry standpoint, i personally don't like them), so i'm not
really "up" on the subject.

hth
 
Hello

thanks for the responses!

Bob Miller said:
Try this datepicker:
http://www.glump.net/dokuwiki/software/microsoft_access_date_picker
It is much easier to use than the ActiveX Calendar control.
Click to expand...

WOW. but a little much. i will have to see how easy it is to adapt to my
situation. thanks for the examples though

David said:
Dawn,
Here is a link with many different calendar options.

http://www.granite.ab.ca/access/calendars.htm
Click to expand...

hate to say it but that links seems to be broken...
 
Hello

Woo Hoo! i did it. i had to come back to the database and add some features
and today i used your suggestion and added the form i had been thinking about
way back. i used one of the date pickers sugested by the other posters, 2
text boxes, one for start and one for end date, then put in the criteria you
suggested into all the queries that are part of the one report i need to have
for the database and it all came together.

thanks for the help! this is a wonderful place to learn how to program!
 
Hello

k, but now i have a new problem. when i go to print the report it starts
asking for the start date and end date all over again. i even tried to do it
by creating a different form and a different button that just printed no
preview. it still asks for the dates again.

what now?
 
k, but now i have a new problem. when i go to print the report it starts
asking for the start date and end date all over again.
Click to expand...

don't really get what you mean by "...all over again". presumably your
report is bound to the query. don't open the query; just open (print or
preview, it doesn't matter) the report. the form has to be open when the
report opens, so that the date controls are available for referencing when
the query runs.

hth
 
Hello

Sorry. i guess i havent made myself clear. as i was thinking about this post
last night i realised that i probably hadnt explained it right.

when i open the report everything is fine. it asks me via a form for the
start and end dates just like i wanted. the problem is that when i go to
print the report by hitting the print button on the tool bar it starts asking
me for the start date and end date again through the "missing" varible
dialog. you know how when you misspell a variable name and it comes up
telling you the name of the variable and then gives you a text box to fill
in, well that is what happens.

now, in this report i have subreports. those subreports are based on
queries. there are 4 subreports and 4 queries, one for each sub report. in
each query i have a date field and in the criteria i have the expression you
gave me to place the star and end date in. i am unsure of the reason it
looses the start date and end date when i go to print, but it has to be
something to do with the fact that they are subreports, does it not?

before i started doing the date thing the report would print fine. it wasnt
until i added the new layer of complexity that it started having this
problem. the thing is i also added some text boxes that would use the start
date and end date to print them on the top of the page. it doesnt ask me for
those start and end dates in the way that the subreports do. instead they end
up with a "?" in them.

is there a property or something i can set to cause the report to remember
the subreports start and end date when i hit the print button?
 
Hello

i should have fully read your post. i see now what the problem is. i closed
the form for the start and end date before printing.

so i need to keep it open. is there a way to cause the closing of the report
to close the form as well? that way a user wont end up having to close it.
 
Hello

yep that fixed the problem. i can print the report now. could i put the
close function on the "on close" of the report? that should then close the
form for me and get the user back to the main form right?
 
yes, you can put a Close command for the *form*, in the *report's* Close
event. make sure you specify the form in the code, as

Private Sub Report_Close()

DoCmd.Close acForm, "FormName", acSaveNo

End Sub

hth
 
Hello

what if i put the save as yes, i already tried it and it worked great. :)
but does the acSaveYes do anything different? i am assuming that the acSave?
just makes sure that any design changes to the form are saved. do i assume
correctly?
 
you don't need to assume, hon. open the form's module, click anywhere in the
"Close" word in the line of code, so that the cursor is within that word.
then press F1. the VBA Help should open directly to that topic, so you can
read up on the arguments. this is a very handy trick when you're working in
the code window, saves you having to do a search in Help.

hth
 
Back
Top