Open Report with two Where conditions (1 Viewer)

sunnytaru

Member
Local time
Yesterday, 22:39
Joined
Mar 24, 2022
Messages
41
Hello Experts,

Not sure if this is possible. I need to open a report with 2 conditions. I have a Form that has a combo box (BarMonCombo) with all Months listed. Then I have a button which has the client name, when user selects month and client name the report for the selected month for that client is generated.

I can make it work with the client name only.

In my query, I have a field LastUpdate which is a date. In the query I have used the Month Name function. Expr: LUMName. Can we not check the BarMonCombo =LUMName as 2nd condition ?

SQL below.

SELECT BARPOList2QRY.ProjectID, BARPOList2QRY.ProjectTitle, BARReportDirectInvoiceQRY.DocumentType, BARPOList2QRY.MemberName, BARReportDirectInvoiceQRY.FundingAmount, BARPOList2QRY.[Payment Schedule Type], BARPOList2QRY.TotalMilestones, BARPOList2QRY.POStatus, BARPOList2QRY.MemberPORecvdDate, BARPOList2QRY.MemberPONum, BARPOList2QRY.MemPOAmt, BARReportDirectInvoiceQRY.PaymentOptionType, BARReportDirectInvoiceQRY.COGInvoicedSchDate, BARReportDirectInvoiceQRY.COGInvoiceNum, BARReportDirectInvoiceQRY.COGInvIssdDate, BARReportDirectInvoiceQRY.COGInvAmt, BARPOList2QRY.Notes, BARPOList2QRY.ActionOn, BARReportDirectInvoiceQRY.Notes, BARReportDirectInvoiceQRY.ActionOn, BARReportDirectInvoiceQRY.LastUpdate, Month([BARReportDirectInvoiceQRY].[LastUpdate]) AS LUMonth, MonthName([LUMonth]) AS LUMName
FROM BARPOList2QRY INNER JOIN BARReportDirectInvoiceQRY ON (BARPOList2QRY.MemberName = BARReportDirectInvoiceQRY.MemberName) AND (BARPOList2QRY.DocumentType = BARReportDirectInvoiceQRY.DocumentType) AND (BARPOList2QRY.ProjectID = BARReportDirectInvoiceQRY.ProjectID);

Please advise.

Thanks
Taruna
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:39
Joined
May 21, 2018
Messages
8,463
I use https://www.dpriver.com/pp/sqlformat.htm but there are lots of other free formatters to make it readable.
SQL:
SELECT barpolist2qry.projectid,
       barpolist2qry.projecttitle,
       barreportdirectinvoiceqry.documenttype,
       barpolist2qry.membername,
       barreportdirectinvoiceqry.fundingamount,
       barpolist2qry.[payment schedule type],
       barpolist2qry.totalmilestones,
       barpolist2qry.postatus,
       barpolist2qry.memberporecvddate,
       barpolist2qry.memberponum,
       barpolist2qry.mempoamt,
       barreportdirectinvoiceqry.paymentoptiontype,
       barreportdirectinvoiceqry.coginvoicedschdate,
       barreportdirectinvoiceqry.coginvoicenum,
       barreportdirectinvoiceqry.coginvissddate,
       barreportdirectinvoiceqry.coginvamt,
       barpolist2qry.notes,
       barpolist2qry.actionon,
       barreportdirectinvoiceqry.notes,
       barreportdirectinvoiceqry.actionon,
       barreportdirectinvoiceqry.lastupdate,
       Month([barreportdirectinvoiceqry].[lastupdate]) AS LUMonth,
       Monthname([lumonth])                            AS LUMName
FROM   barpolist2qry
       INNER JOIN barreportdirectinvoiceqry
               ON ( barpolist2qry.membername =
                    barreportdirectinvoiceqry.membername )
                  AND ( barpolist2qry.documenttype =
                        barreportdirectinvoiceqry.documenttype )
                  AND ( barpolist2qry.projectid =
                        barreportdirectinvoiceqry.projectid );
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:39
Joined
May 21, 2018
Messages
8,463
in your combo you should have two columns
1 January
2 Febuary
3 March

The first column is bound and hidden
then in your criteria
"LuMonth = " & Me.BarMonCombo
no need for the name in the query.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:39
Joined
May 21, 2018
Messages
8,463
However if you want to just use the name make sure you enclose it in single quotes
" LumName = '" & me.barmonCombo & "'"
where the combo is bound to the month name not the month value.
 

sunnytaru

Member
Local time
Yesterday, 22:39
Joined
Mar 24, 2022
Messages
41
However if you want to just use the name make sure you enclose it in single quotes
" LumName = '" & me.barmonCombo & "'"
where the combo is bound to the month name not the month value.
When I am adding the above under criteria the combobox list month name disappear. If I add as where condition in the MACRO Report for all months and all clients is generated.
1656119523656.png
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 02:39
Joined
Sep 21, 2011
Messages
14,048
Yout syntax appears to be incorrect for the WHERE?
I do not use macroes, well hardly at all, so the syntax might be different

I would try however
==MemberName = 'CNNO' AND LumName = & Forms!..... (I am not going to type all that out :) )
 

sunnytaru

Member
Local time
Yesterday, 22:39
Joined
Mar 24, 2022
Messages
41
Yout syntax appears to be incorrect for the WHERE?
I do not use macroes, well hardly at all, so the syntax might be different

I would try however
==MemberName = 'CNNO' AND LumName = & Forms!..... (I am not going to type all that out :) )
Hello Gasman, it did not work
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:39
Joined
Sep 21, 2011
Messages
14,048
So try each in turn, to see what does work. Or use VBA.
 

GPGeorge

Grover Park George
Local time
Yesterday, 19:39
Joined
Nov 25, 2004
Messages
1,776
Hello Gasman, it did not work
Often, we have a better chance of providing useful feedback when we know what DID happen as well as what you expected to happen, not just "it did not work". In other words, "did not work" is a statement of an outcome, but what is needed to diagnose a problem is usually the details of what events and actions occurred along the way. Was an error raised? Did the wrong result appear? Did nothing at all happen? That kind of details.
 

sunnytaru

Member
Local time
Yesterday, 22:39
Joined
Mar 24, 2022
Messages
41
Often, we have a better chance of providing useful feedback when we know what DID happen as well as what you expected to happen, not just "it did not work". In other words, "did not work" is a statement of an outcome, but what is needed to diagnose a problem is usually the details of what events and actions occurred along the way. Was an error raised? Did the wrong result appear? Did nothing at all happen? That kind of details.
Sorry about the half info, It gives an exclamation sign
1656166055886.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:39
Joined
Sep 21, 2011
Messages
14,048
This works for me. Plus you should use the Builder to get the correct control values.
Honestly though, learn VBA, so much easier than having to save macro every 10 seconds to run it. :(

I did say the syntax was dfferent. :mad:

Also here is the syntax for text in the field

[REFERENCE]='REA1180' And [Client]=[Forms]![frmEmails]![Client]
1656167079676.png
 
Last edited:

sunnytaru

Member
Local time
Yesterday, 22:39
Joined
Mar 24, 2022
Messages
41
This works for me. Plus you should use the Builder to get the correct control values.
Honestly though, learn VBA, so much easier than having to save macro every 10 seconds to run it. :(

I did say the syntax was dfferent. :mad:

Also here is the syntax for text in the field

[REFERENCE]='REA1180' And [Client]=[Forms]![frmEmails]![Client]
View attachment 101432
1656272727376.png

This above snap is how I added in the Where condition but below error received

1656272685575.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:39
Joined
Sep 21, 2011
Messages
14,048
Pretty much why I avoided macroes.
So when you use one criteria, do you use two = signs or one on the left of the criteria? and it works?
 
Last edited:

Similar threads

Users who are viewing this thread

Top Bottom