Access 97
I am building a form that will let managers make and edit a Report. I have the parameter in the main form working but
need a parameter in the sub form to work which I cant seem to get to work.
am adding an attachment…problem is at open frmopening, then click on report button, then click on make report button
In the drop down box call up Browns1 click run query button you will get 288 records now go back and try to make
it so you can put a date in Begin Date and see if you can get less than 288 records and then add a Des No with no Date…..What i need is if a Begin Date is place in the sub form then in the query tmdate I need those date greater than and if there is a second Des No with no date well for that des no., I need all records no matter what date is with the records
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Parameters query subform using date greater than (Access 97)
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Parameters query subform using date greater than (Access 97)
- This topic has 17 replies, 4 voices, and was last updated 22 years, 6 months ago.
AuthorTopicWSGreg
AskWoody LoungerOctober 22, 2002 at 11:54 am #378301Viewing 0 reply threadsAuthorReplies-
WBell
AskWoody_MVPOctober 22, 2002 at 9:11 pm #625870We need a bit more detail in order to help you. Is Begin Date a parameter in a query, a field in the data displayed in the subform, or a control on the form? What does you table structure look like – I presume there must be some relationships for you to use a subform? What is Des No? What is Browns1? What does “adding an attachment” have to do with your problem?
-
WSGreg
AskWoody LoungerOctober 22, 2002 at 11:57 pm #625903Thanks Wendell for reponding, This morning I was going to attach what I was working on thinking it would be easier
than just explaning but because you ask I am going to try,I have made a form that is going to let managers name their own reports so they can edit it or keep it and or make
more reports. What they do is give it a name which goes into a table, this field that they give a name shows up on the form
as a combo box drop down so they can reselect it to edit it or leave it like it is thats what browns1 was if you could
have gotten my attachment.Now after they have given a name and selected it, a sub form is activated which gives them a chance to select a Des no.
Des no is just a number that is related to project information in the sub form this number is selective from a drop down also.
Now I am working on a query base on this form to be able to run a report.
The des no has information that sometime contains a date but not always. I have the query working when a des no is
selected it will bring up all the records related to that des no, now here’s my problem in the sub form I have a field
so a date can be enter….(keep in mind that more than one des no can be selected) and with this des no. I have a field
so a date can be related to a des no. if the managers wants a date with it ….if they do then they will want all records with
that des no. equal to and greater than the date ….trying to add in the date is when my query breaks down and does not
work giving wrong information…..
I have tried >[forms]![subformname]![begin Date] in the query of the field that contains the dateI have two thoughts as to way my query is not working when a date is added one it might have
something to do with a subform or I need some kind of IIF statement because of null information
in the date fields dealing with the des no. but realize too that in this sub form some des no
the managers wont put in a date and some they will put in a date so I think I also need an IIF statement for
that toonow do you have enought or is this still too confusing?
Greg
-
WSGreg
AskWoody Lounger -
WScharlotte
AskWoody LoungerOctober 23, 2002 at 1:46 am #625927(Edited by charlotte on 22-Oct-02 19:46. Afterthoughts)
You aren’t referencing the subform correctly for starters. You need the name of the parent form in there first:
Forms!ParentFormName!SubformName!ControlName
Oops! I just noticed the Access 97 version. For A97, you’ll need this syntax:
Forms!ParentFormName!SubformName.Form!ControlName
-
WSHansV
AskWoody LoungerOctober 23, 2002 at 10:23 am #626008In my Access 97 SR2, the following expressions all work OK:
Forms!ParentFormName!SubformName!ControlName
Forms!ParentFormName!SubformName.Form!ControlName
Forms!ParentFormName!SubformName.Form.ControlNameThey also work if you replace the bang ! between ParentFormName and SubFormName by a period .
What doesn’t work in Access 97, is
Forms!ParentFormName!SubformName.ControlName
Forms!ParentFormName.SubformName.ControlName -
WSGreg
AskWoody LoungerOctober 23, 2002 at 12:27 pm #626036Alright !!!! thanks a bunch all! I like it one step at a time …..the following got working a Date parameter
[Forms]![ParentFormName]![SubformName]![ControlName]Now on with the other problem when I place a Des no without a Date I get back a null answer if I
try to use one with a date and one without a date, I still get a null answerBecause I am trying to be able to use a parameter with a date and without a date I think this maybe a double IIF
I am not sure how to do the double IIF let alone the single IIF …. I would think its going to be something likeIIf ([forms]![frmreportnew]![GCDESIGN subform2]![Begin] = is Null,
-
WSHansV
AskWoody LoungerOctober 23, 2002 at 1:14 pm #626047There is a strange trick you can use for this. It was discussed in some detail in the thread starting with post 171726. It involves adding a dummy column to the query to test for an empty date. See the attached picture. Of course, you will have to substitute the actual names you use.
You will probably have to declare the date parameter explicitly.
In the design view of the query, select Query/Parameters…
Copy the date parameter exactly as it is in the query grid to the Parameters box in the first row.
Select Date/Time in the Data Type box.
Click OK. -
WSGreg
AskWoody LoungerOctober 23, 2002 at 5:04 pm #626119Hans Thank for posting that post His question seem to be exactly what I am trying to do but I must be doing things he wasnt like trying to get more than one des no, I guess it worked for him because he only went after one thing at a time…..I have read all of them and kept trying different ones but not getting the results that I want
The best I can get it to show me is one des no., with a date or no date but if I have more than one des no., it doesnt work right
So I am going to attach showing the tables in the query and the last thing I tried
The table report is just to make a name to call up parameters…the table GCDESIGN was made so I could make the subform
is a field that the manager can type in a Name…remember in the sub form Des No is a drop down….One other thing
I also tried Hans, just like your showed by removing the GCDESIGN and report tables but that would bring in too many
des no….most of the time when I was getting too many extra des no was coming from other test reports that I had made -
WSGreg
AskWoody Lounger -
WSHansV
AskWoody LoungerOctober 24, 2002 at 7:23 am #626363Greg,
I am confused by your setup.
Is GCDESIGN a temporary table meant to be used only for the selection of a report? In that case, you should delete all records before making a new selection; otherwise, you will keep on accumulating the selections.
Since Begin is a field in this table, the user will be able to enter a different date for each Des No. Is that want you intend, or do you want one begin date to combine with the Des no’s?
I think you can omit the Report table from the query (of course, the Reportname field must be from the GCDESIGN table then).
-
WSGreg
AskWoody LoungerOctober 29, 2002 at 11:59 am #626414I think I see what you mean about the report table. Yes want the begin date to be different for each des no and
some cases no date will be put in. I dont know how to answer your question about ” Is GCDESIGN a temporary table”
it is meant to be able to hold certain des numbers for as long a manager wants and it also there so that more than
one manager can have their des number and yes this table is only for the report nothing to do with data storing. -
WSHansV
AskWoody LoungerOctober 24, 2002 at 1:23 pm #626421In so far as I am able to understand your description, the selection criteria (from the subform) are stored in the GCDESIGN table.
- You only want those [Des no] that have been entered in the subform (and therefore in the GCDESIGN table). In the query design, you have an left join from DESCRIP to GCDESIGN. This means that all records from DESCRIP will be returned, regardless of whether there is a matching [Des no] in GCDESIGN. I think you need an inner join. You will probably need to change other joins in the query to inner joins too.
- I had misunderstood your setup in a previous reply. The criteria for tmdate in the query design should not refer to the subform, but to the Begin field in the GCDESIGN table. Replace [Forms]!
-
WSGreg
AskWoody LoungerOctober 28, 2002 at 11:58 am #627089Hans I am back on this now if It takes the rest of the week!
Do all managers share the same GCDESIGN table,
YES
or does each manager have a private copy of this table? If there is one table, it will contain the accumulated selections made by all managers.
How are you going to distinguish between the entries made by each, unless you delete the existing records before allowing a new selection?
This is the reason I created the report Table and reportname field Not only does this give each managers their own report name the way they want it….but It should let them set up more than one report….Hence Browns1(des nos 8967533 and 9048484) ….Browns2(des no 900001 and 900002 and 900003)….Browns3(des no 8900001 and 8900002 and 8900003)
-
WSHansV
AskWoody Lounger -
WSGreg
AskWoody LoungerOctober 28, 2002 at 5:00 pm #627155It took making a query then building another query on the first query and a table.
Hans and all thanks got the query to work….I used[the table name] with Dum:[the table name] …Is Not Null Is Null but in the query
I removed the report table that would give me every thing in the reports table and make the date critria work right called this query qrydesignercostreport2…..but to get the results I was looking for I then had to make a query with qrydesignercostreport2 query and the report table to get just one
reportnamesql of query:SELECT report.reportname, qrydesignercostreport2.Des, qrydesignercostreport2.route_number, qrydesignercostreport2.location, qrydesignercostreport2.fname, qrydesignercostreport2.mname, qrydesignercostreport2.lname, qrydesignercostreport2.tmdate, qrydesignercostreport2.HrRate, qrydesignercostreport2.regtime, qrydesignercostreport2.overtime, qrydesignercostreport2.dataid
FROM qrydesignercostreport2 RIGHT JOIN report ON qrydesignercostreport2.reportname = report.reportname
WHERE (((report.reportname)=[forms]![frmreportnew]![Combo6]));The reportname is being selected from…… Combo6
Once again thanks all who responded…read all posts and all posts helped me out!
here is sql for qrydesignercostreport2
PARAMETERS [forms]![frmreportnew]![GCDESIGN subform2]![Begin] DateTime;
SELECT GCTMDATA.Des, DESCRIP.route_number, DESCRIP.location, GCNAME.fname, GCNAME.mname, GCNAME.lname, GCTMDATA.tmdate, GCTMDATA.HrRate, GCTMDATA.regtime, GCTMDATA.overtime, GCTMDATA.dataid, GCDESIGN.Begin AS dum, GCDESIGN.reportname
FROM ((GCTMDATA LEFT JOIN GCNAME ON GCTMDATA.CONTACT_IDENTIFIER = GCNAME.CONTACT_IDENTIFIER) RIGHT JOIN DESCRIP ON GCTMDATA.Des = DESCRIP.DESNO) RIGHT JOIN GCDESIGN ON DESCRIP.DESNO = GCDESIGN.DesNo
WHERE (((GCTMDATA.tmdate)>=[Begin]) AND ((GCDESIGN.Begin) Is Not Null)) OR (((GCDESIGN.Begin) Is Null)); -
WScharlotte
AskWoody Lounger
-
-
-
WBell
AskWoody_MVPOctober 23, 2002 at 10:06 am #626004Well, I agree that it’s a bit confusing, and your are trying to do a pretty complicated task. Never the less, I think Charlotte has identified your difficulty with the criteria – it’s a difference between Access97 and later versions. Yes, you will probably need some logic to check for null versus there being a date, but the first issue is getting your query to check the date field correctly.
-
-
Viewing 0 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Pwn2Own Berlin 2025: Day One Results
by
Alex5723
1 minute ago -
Windows 10 might repeatedly display the BitLocker recovery screen at startup
by
Susan Bradley
55 minutes ago -
Windows 11 Insider Preview Build 22631.5409 (23H2) released to Release Preview
by
joep517
2 hours, 11 minutes ago -
Windows 10 Build 19045.5912 (22H2) to Release Preview Channel
by
joep517
2 hours, 13 minutes ago -
Kevin Beaumont on Microsoft Recall
by
Susan Bradley
9 hours, 14 minutes ago -
The Surface Laptop Studio 2 is no longer being manufactured
by
Alex5723
10 hours, 20 minutes ago -
0Patch, where to begin
by
cassel23
4 hours, 22 minutes ago -
CFPB Quietly Kills Rule to Shield Americans From Data Brokers
by
Alex5723
23 hours, 58 minutes ago -
89 million Steam account details just got leaked,
by
Alex5723
11 hours, 44 minutes ago -
KB5058405: Linux – Windows dual boot SBAT bug, resolved with May 2025 update
by
Alex5723
1 day, 8 hours ago -
A Validation (were one needed) of Prudent Patching
by
Nibbled To Death By Ducks
23 hours, 30 minutes ago -
Master Patch Listing for May 13, 2025
by
Susan Bradley
10 hours, 37 minutes ago -
Installer program can’t read my registry
by
Peobody
5 hours, 35 minutes ago -
How to keep Outlook (new) in off position for Windows 11
by
EspressoWillie
21 hours, 17 minutes ago -
Intel : CVE-2024-45332, CVE-2024-43420, CVE-2025-20623
by
Alex5723
1 day, 4 hours ago -
False error message from eMClient
by
WSSebastian42
1 day, 19 hours ago -
Awoke to a rebooted Mac (crashed?)
by
rebop2020
2 days, 4 hours ago -
Office 2021 Perpetual for Mac
by
rebop2020
2 days, 5 hours ago -
AutoSave is for Microsoft, not for you
by
Will Fastie
1 day, 2 hours ago -
Difface : Reconstruction of 3D Human Facial Images from DNA Sequence
by
Alex5723
2 days, 9 hours ago -
Seven things we learned from WhatsApp vs. NSO Group spyware lawsuit
by
Alex5723
1 day, 10 hours ago -
Outdated Laptop
by
jdamkeene
2 days, 14 hours ago -
Updating Keepass2Android
by
CBFPD-Chief115
2 days, 20 hours ago -
Another big Microsoft layoff
by
Charlie
2 days, 19 hours ago -
PowerShell to detect NPU – Testers Needed
by
RetiredGeek
17 hours, 21 minutes ago -
May 2025 updates are out
by
Susan Bradley
1 hour, 13 minutes ago -
Windows 11 Insider Preview build 26200.5600 released to DEV
by
joep517
3 days, 2 hours ago -
Windows 11 Insider Preview build 26120.3964 (24H2) released to BETA
by
joep517
3 days, 2 hours ago -
Drivers suggested via Windows Update
by
Tex265
3 days, 1 hour ago -
Thunderbird release notes for 128 esr have disappeared
by
EricB
21 hours, 57 minutes ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.