Dlookup in report based on 3 criteria's not working 2

[cimoli]

I have a Dlookup in a header. There are 3 table fields involved from tblReport :
Resort# eg 510, ReportNum eg 20 and BusnID eg 1. They are all number fields in tblReport.

They help find the text field Body1 from tblReport. Body1 has a few sentences.

Here is what is in the report rptTicketSplinterInd Text Box name called text132:

=Space(2) & DLookUp("[Body1]","tblReport","[tblReport]![Resort#] = " & [rptTicketSplinterInd]![ResortID] & " And [[tblReport]![ReportNum]=20" & "And [tblReport]![BusnID]=[[rptTicketSplinterInd]![BusnID]")

(it helps me show the tbl and rpt in the calc so that i can learn better.)

I get an error that says #Type.

I bet that i have my quotes all wrong. Can you help me? Thanks Cimoli

 

[cimoli]

The following used to Work for me until I added the BusnID field. Here is what i had earlier.

=Space(4) & DLookUp("[Body1]","tblReport","[Resort#] = " & [Report]!ResortID & " And [ReportNum]=20")

So I bet i messed up when i tried to add BusnID into the mix. Below is where i stand now.
It does not work though.

=Space(2) & DLookUp("[Body1]","tblReport","[tblReport]![Resort#] = " & [rptTicketSplinterInd]![ResortID] & " And [[tblReport]![ReportNum]=20" & "And [tblReport]![BusnID]=[[rptTicketSplinterInd]![BusnID]")

cimoli

 

[cimoli]

Maybe I have a solution. Seems to work again.

Yell if you see an area to improve.

=Space(0) & DLookUp("[Body2]","tblReport","[Resort#] = " & [Report]![ResortID] & " And [tblReport].[ReportNum]=10" & " and [tblReport]![BusnID]=[Report]![BusnID]")

thanks for looking. Cimoli

 

[dhookom]

I find it hard to believe this works since there is no table [Report] in the domain of the DLookup():

=Space(0) & DLookUp("[Body2]","tblReport","[Resort#] = " & [Report]![ResortID] & " And [tblReport].[ReportNum]=10" & " and [tblReport]![BusnID]=[Report]![BusnID]")

Did you copy and paste this?
This is what I expect should work:

=DLookUp("[Body2]","tblReport","[Resort#] = " & [Report]![ResortID] & " And [ReportNum]=10 and [BusnID]=" & [Report]![BusnID])

Duane
Hook'D on Access
MS Access MVP

 

[cimoli]

Duane - I will go with your method. As usual. Mine seemed to work but i am not taking any chances on my stuff!!

I will donate for Glen.

Cimoli

 

[cimoli]

Duane - an afterthought. I am trying a new tack of having the Dlookup inside the Qry and not in the report.

The Body1 field in the tblReport is a MEMO field. This field has about 3 sentences in it.

The Qry shows the Body1 just fine; complete sentence shown fine. I put the Body1 field into the report. However, the report now cuts off some of the sentence. The report shows about 90% of sentence. I wonder why?

The qry shows the full sentence. The query field Body1 uses your fine calc.

Body1: DLookUp("[Body1]","tblReport","[Resort#] = " & [tblReservation]![ResortID] & " And [ReportNum]=20 and [BusnID]=" & [tblReservation]![BusnID])

Thanks for the advise on how to get the report to show a full paragraph for the Body1 field.

Cimoli

 

[PHV]

Why use DLookUp in the query instead of a JOIN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

To PHV:
If I write too much here, I can understand if you want to pass on my situation.

That is a good question, and I tried again today to see if I could connect tblReport to the tblReservation. 2 of the 3 fields would match up. Like you are thinking. BusnID and ResortID.
However, the 3rd field ReportNum is only in tblReport.

Here is some background should you see something that I do not see.

The form button to generate the report has an On Click Event.
The purpose is for the VBA to find the name of which 2 reports to use depending upon some criteria.
Once it dloopups into tblReport to find the desired Report name, it runs the report.
then behind the scenes, the related qry for that report, gets the Body1, Body2 etc senetences needed for that hotel report. Many hotels have different sentences. Hence why I put them into tblReport.

The form button has :

Private Sub Command30_Click()
'keep
Dim lResort As Long, sReportNameSpl As String, sReportNameInd As String
lResort = Me.txtResortID


If Nz(DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=20"), "") = "" Then
MsgBox "No Resort Available"
Exit Sub
Else
sReportNameSpl = DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=20")
End If

sReportNameInd = DLookup("ReportName", "tblReport", "[Resort#]=" & lResort _
& " AND [ReportNum]=10")

If Not IsNull(Forms![frmReservation]!txtSplinterDateIN) Then
DoCmd.OpenReport sReportNameSpl, acViewPreview
Else
DoCmd.OpenReport sReportNameInd, acViewPreview
End If

End Sub


I did not write the above vba, so feel free to criticize.


FYI, the tblReport has fields:
BusnID - number field
Resort# - number (i wished i had used reportID but too late now)
ReportNum - number
ReportName - text
Body1 - memo
Body2 - memo
Body3 - memo
Body4 - memo
Body5 - memo
Body6 - memo
Body7 - memo
Body8 - memo

Here is the catch: There are 4 ReportNum styles in tblReport for each hotel and busn. For example: busnID, resortID, reportNum.
1 510 10
1 510 20
1 510 30
1 510 40

2 510 10
2 510 20
2 510 30
2 510 40


In summary: i thought of joining tblReservation and tblReport via BusnID and ResortID (resort#) that is in both tables, however, there are 4 reportNum's. So if i joined, it would not join on just the 1 desired reportnum. my records would multiple by 4 incorrectly.

Maybe I could try and see if I can reportNum into tblReservation somehow??
as of now, i can't think of how.


Anyway, if you still want to investigate this, thanks.
So this is why i went to Dlookups. Maybe you will unlock the complicated process.

As of now, i put the dlookups for Body1, body2 etc into the qry.
But now the report using those qry fields are using just 80 to 90% of the sentences of Body1 field. it cuts them off in the report. and body1 is a memo field. so i don't get it.
thanks
Cimoli

 

[cimoli]

Well. Some hotel reports Body1, Body2 etc work fine and do not cut off the sentences. It may be just 1 place. My boss wrote the sentences in Word and copy pasted them into the tblReport Body1 field. I tried to retype them but get the sentence chopped for this 1 hotel report. It is a memo field. I will keep trying things.

I am still interested to see if you see a way to do a Join instead of the many dlookups.

cimoli

 

[PHV]

Join tblReservation and tblReport via BusnID and ResortID and use a criteria for reportNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

PHV - funy but i started to do that this mornign but chickened out!! I will do what you say. thanks so much. should work like you say. cimoli