How to pull a single record onto a report with query?

P

pokdbz

I have this query below where I need to use the SSN on the current form to
open a report which uses this query. So how do I do this?

SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));
 
J

Jerry Porter

There are several ways to do it, depending on where the Where goes. A
simple way is to include it in the command that opens the report.

If you're using a macro, you can include the condition in the "Where
Condition" property. If the form is named MyForm, it would look
something like:
(SSN = Forms!MyForm!currentSSN) AND (AlcoholDate = Date())

If you're opening the report (call it MyReport) from a button in the
form, it might look like:
DoCmd.OpenReport MyReport,,,,"(SSN = '" & Me!currentSSN & "'") AND
(AlcoholDate = Date())"

Note: The quotes may not be easily readable. Here they are with extra
spaces: ... SSN = ' " & ME!currentSSN & " ' ") AND ...

Jerry
 
P

pokdbz

I think this is the one that I would probably use:
DoCmd.OpenReport MyReport,,,,"(SSN = '" & Me!currentSSN & "'") AND
(AlcoholDate = Date())"

So on my form there is a field called SSN and in the query I have the
[currentSSN]. So the way that you have it set up now is this right? I am
not sure I understand this part of it ,,,,"(SSN = '" & Me!currentSSN & "'")
 
J

Jerry Porter

It looks like I got it backwards. If the control on your form is called
SSN, and the field in table or query that your Report is based on is
currentSSN, they you want:

DoCmd.OpenReport MyReport,,,,"(currentSSN = '" & Me!SSN & "'") AND
(AlcoholDate = Date())"

Here's the explanation:
Let's say that the value of SSN in the form is 123456789. Then Me!SSN
will be replaced by "123456789". So the expression would look to Access
like
"(currentSSN = '" & "123456789" & "'") AND (AlcoholDate = Date())"
This is just several strings joined together, so it's the same as
"(currentSSN = '123456789') AND (AlcoholDate = Date())"

This is now a valid Where clause that will be added to the query that
your report is based on.
The point of the combined strings and the different quotes is to allow
you to get your data from the form into the query with single quotes.
We use the single quotes because it's even harder (though possible) to
make it work with double quotes.

Jerry
 
J

Jerry Porter

Actually, it seems to me like I had too many commas.

DoCmd.OpenReport MyReport,,,"currentSSN = '" & Me!SSN & "'"
or for preview mode (good for testing):
DoCmd.OpenReport MyReport,acViewPreview,,"currentSSN = '" & Me!SSN &
"'"

If the command is set up right, but a dialog asks for currentSSN, it
means that currentSSN is not the correct field name in the report.
 
P

pokdbz

I tried the one with the preview and still didn't work. It still brings up
the box. And I did check on the form and the field is named SSN. SSN is
Text if that makes a difference. Any other suggestions?
 
A

Albert D.Kallal

Note the following suggestion:

so, the code should look like

dim strWhere as string

strWhere = "SSN = '" & me!SSN & "'"

' now the date part.

strWhere = strWhere & " AND AlcoholDate = #" & format(date(),"mm/dd/yyyy") &
"#"

DoCmd.OpenReport MyReport,acViewPreview,,strWhere

A few things:

strWhere = "SSN = '" & ME!SSN & "'"

The SSN above is course the name of the field in the table.

The me!SSN is the name of the field on your form.

Remember, for your original query, remove all parameters...as you now don't
need them (so, remove all of the conditions..since we now pass them to the
report)

Note the above example of course will return the record based on today's
date...
 
P

pokdbz

I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));

Here is the code:
Dim stDocName As String
stDocName = "Alcohol_Pull"
Dim strWhere As String
strWhere = "SSN = '" & Me!SSN & "'"

strWhere = strWhere & " AND AlcoholDate = #" & Format(DATE, "mm/dd/yyyy") &
"#"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

It is still coming up with the box that says to enter currentSSN
 
A

Albert D.Kallal

I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));
Click to expand...


Did you bother to even read my last post?

I will re-post:
need them (so, remove all of the conditions..since we now pass them to the
report)

The above says to remove all parameters from the query as you now don't need
them. I really not sure how to state that in a different language. What this
means to do is to REMOVE all conditions from the query. To quote from above
AGAIN:
report)

So, you query should look like:
 
P

pokdbz

Ok it is pull it up but not with just the current date it is pulling every
date.

Albert D.Kallal said:
I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));
Click to expand...


Did you bother to even read my last post?

I will re-post:
need them (so, remove all of the conditions..since we now pass them to the
report)

The above says to remove all parameters from the query as you now don't need
them. I really not sure how to state that in a different language. What this
means to do is to REMOVE all conditions from the query. To quote from above
AGAIN:
report)

So, you query should look like:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
Click to expand...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
P

pokdbz

it is just pulling everything up no matter what SSN and date

pokdbz said:
Ok it is pull it up but not with just the current date it is pulling every
date.

Albert D.Kallal said:
I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));
Click to expand...


Did you bother to even read my last post?

I will re-post:
Remember, for your original query, remove all parameters...as you now
don't
Click to expand...
need them (so, remove all of the conditions..since we now pass them to the
report)

The above says to remove all parameters from the query as you now don't need
them. I really not sure how to state that in a different language. What this
means to do is to REMOVE all conditions from the query. To quote from above
AGAIN:
(so, remove all of the conditions..since we now pass them to the
Click to expand...
report)

So, you query should look like:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
Click to expand...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
 
P

pokdbz

Sorry I forgot to put in the link material. Got it working. Thanks for the
help.

pokdbz said:
it is just pulling everything up no matter what SSN and date

pokdbz said:
Ok it is pull it up but not with just the current date it is pulling every
date.

Albert D.Kallal said:
I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));


Did you bother to even read my last post?

I will re-post:

Remember, for your original query, remove all parameters...as you now
don't
need them (so, remove all of the conditions..since we now pass them to the
report)

The above says to remove all parameters from the query as you now don't need
them. I really not sure how to state that in a different language. What this
means to do is to REMOVE all conditions from the query. To quote from above
AGAIN:

(so, remove all of the conditions..since we now pass them to the
report)

So, you query should look like:

SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
Click to expand...
Click to expand...
 
P

pokdbz

By chance do you know how to do this on a subreport? I have a Main report
called MainReport that has subreports on it and the Alcohol one that you got
working is on it. So how do you do it for subreport because I have like 3
more that I have to do this with?

Albert D.Kallal said:
I am still having this same problem.
Is there something wrong with my query:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
WHERE (((Alcohol.SSN)=[currentSSN]) AND ((Alcohol.AlcoholDate)=Date()));
Click to expand...


Did you bother to even read my last post?

I will re-post:
need them (so, remove all of the conditions..since we now pass them to the
report)

The above says to remove all parameters from the query as you now don't need
them. I really not sure how to state that in a different language. What this
means to do is to REMOVE all conditions from the query. To quote from above
AGAIN:
report)

So, you query should look like:
SELECT Alcohol.SSN, Alcohol.AlcoholDate, Alcohol.Alcohol_01,
Alcohol.Alcohol_02, Alcohol.Alcohol_03, Alcohol.Alcohol_04,
Alcohol.Alcohol_05, Alcohol.Alcohol_06
FROM Alcohol
Click to expand...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
[email protected]
http://www.members.shaw.ca/AlbertKallal
Click to expand...
 
A

Albert D.Kallal

Great stuff....(sounds like you got something starting to work!!).
By chance do you know how to do this on a subreport? I have a Main report
called MainReport that has subreports on it and the Alcohol one that you
got
working is on it. So how do you do it for subreport because I have like 3
more that I have to do this with?
Click to expand...

You can, but do remember, if those sub-reports have a relation (one to many)
to the main report, then you should only get those "many" records for the
main records that we restricted (by using our where clause). So, hopefully,
if you setup the link child/master fields, then restricting the records to
the main repot should restrict the records to the sub-report.

So, you *can* very well restrict the records in the sub-reports. Further, if
those sub-reports are to have the SAME restricting as the main report, then
again you an use the link master/child fields. (so, it might not be one to
a many, but a one to one).

So, hopefully, you have some means to relate those sub-reports to the main
report (for example, if the sub-reports need the SAME restrictions, and same
records as the main report, then simply use the link master/child fields in
the sub-report control by the primary key. This will result in the
sub-reports having the SAME filter as the main form.

If you need to filter each sub-report on a different criteria then the main
reports "where" filter, then the link master/child settings might not help
you (but, if you can't use the link master/child settings, then likely you
don't have a proper relation between the main and sub form anyway. So, you
*can* do this, but you can't use the "where" clause for the sub-forms. You
will actually have to set the sub-forms reocrdsouce in the open event of the
report.

So, clarify the above issue. Since setting the filter for the sub-forms is
easy, and automatic if you use the link master/child settings. (this also
means that your sub-reports queries don't need any parameters at all. If you
did/do have a bunch of parameters for the sub-reports, then can see now that
you done way too much work....and simply using a where clause for the main
reprot is the simple, and easy way to go....

Good luck, and do feel free to ask for more info on the above....
 
P

pokdbz

Ok, I'm a little confused but I think that I get the general idea.
OK the main report is Alcohol_Pull with the record source being Alcohol_Pull

One of the subreports that I set up using the wizard:

PHQ_Pull
Source object: Report.PHQ_Pull
Link Child fields: SSN;PHQDate
Link Master fields: SSN;AlcoholDate

I think that this is the right way but not sure. The subreport is setup the
same way as the other one that you were helping me out with. I need to pull
the SSN and current date for this one. So it is the same but a subreport.
When I preview the report by itself it comes up with 4 boxes asking for
currentSSN
currentDate
currentSSN
currentDate

When I put in the same information for both it pulls the right record the
way that I want it too. Now I think that I have the report setup right?
Correct me if I am wrong.

Now using the code that you provided me for the previous problem it still
brings up that information but does not show what is suppose to be in the
subreport.
It also brings up those boxes again but 6 of them this time:
currentSSN
currentDate
currentSSN
currentDate
currentSSN
currentDate


So am I going about this the right way? Thanks for being so patient and
helping out so much.
 
A

Albert D.Kallal

Ok, I'm a little confused but I think that I get the general idea.
OK the main report is Alcohol_Pull with the record source being
Alcohol_Pull

One of the subreports that I set up using the wizard:

PHQ_Pull
Source object: Report.PHQ_Pull
Link Child fields: SSN;PHQDate
Link Master fields: SSN;AlcoholDate

I think that this is the right way but not sure.
Click to expand...

Ok, the only thing not mentioned for the sub-report is what is the data
source, or table/query it is based on ?

I think you are on the right track here, but it is not clear if that
sub-report is based on the same table (or query) as the main report? (is
it?). I mean, if the table is the same name, then the field names in the
above master/child link would be the same name...right?

Since your field names are different, then I have to logically assume your
sub-report tables are different tables (and, this is reasonable, and often
the case for most sub-reports). I just want to be sure we are on the same
page here.

And, if they are the same tables, then I would join them on a autonumber id
field if you have one.
The subreport is setup the
same way as the other one that you were helping me out with. I need to
pull
the SSN and current date for this one. So it is the same but a subreport.
Click to expand...
When I preview the report by itself it comes up with 4 boxes asking for
currentSSN
currentDate
currentSSN
currentDate
Click to expand...

You should not get any prompts. Either the link field names are wrong, or
you still got some parameters in the actual queries used (and, as mentioned,
we don't need them anymore). Our problem now is we are trying to remove a
bunch of extra stuff we did not need in the first place.
It also brings up those boxes again but 6 of them this time:
currentSSN
currentDate
currentSSN
currentDate
currentSSN
currentDate


So am I going about this the right way? Thanks for being so patient and
helping out so much.
Click to expand...

Those above prompts means you have parameters in the sql somewhere.
Remember, our sql does not have any parameters anymore. You can try opening
the sub-report all by its self, and see if you get any prompts. So, get the
sub-report opening without any prompts. When you get that working, you can
close down the sub-report,a nd then go back to working on the "main" report
that has that sub-report.

As mentioned, if they are the same table, then you can a "id" autonumber
for the link between the main report, and the sub-reports, then the
sub-reports will filter automatically for you...
 
P

pokdbz

Yes they are all different tables for the subreports. They have 2 things in
common SSN and a Date field. In each of the tables the SSN is the field name
and the Date field names are different for each table say AlcoholDate,
PHQDate, SFDate.
Yes I think all of the subreports should be based off of the main report
because they need the same things the SSN and the currentDate.
So every report and supreport needs to pull the SSN and current date.

Now how do I go about setting this up to pull the SSN from the current form
and open the report with the subreports on it?
 
P

pokdbz

OK, I think that I got the Subreports working also. Thanks for all of your
help. I will let you know if there are any more problems. :)
 
Top