Qery based on combox to show all records (1 Viewer)

PhilUp

Registered User.
Local time
Today, 00:28
Joined
Mar 4, 2015
Messages
60
Hi everybody,

I have bee stuck on this for two weeks now.

I am traying to have a query sort out my invoices by year but also to have the possibility to show all invoices.

I have one table "INVOICE" where I have a column "YEAR" calculated with DatePart function from the invoice date.

On my form "INVOICE LISTING", I want to have a combox "Combo957" selecting the year. I have forced the "ALL" selection to the combox using a UnionQuery.

My problem, I cannot get the query to work. I have tried many ways, the closest I can get is :

IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])

If I select the year from the combo, it works, if I select "ALL" nothing is shown.

here is the full SQL

SELECT DISTINCTROW Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]) AS DueYear, [Invoice]![month] & " - " & [Invoice]![Year] AS InvoiceMonth
FROM (Companies INNER JOIN Sales ON Companies.CompanyID = Sales.CompanyID) INNER JOIN Invoice ON Sales.SaleID = Invoice.SaleID
WHERE (((Invoice.Year)=IIf([Forms]![Invoice Listing]![Combo957]="ALL",([Invoice].[Year])<Year(Date()),[Forms]![Invoice Listing]![Combo957])))
GROUP BY Companies.Company, Companies.City, Companies.Country, Sales.Brand, Sales.Type, Sales.Date, Sales.QuoteNo, Sales.Delivered, Sales.Account, Sales.Branch, Invoice.InvoiceNumber, Invoice.InvoiceDate, Invoice.CustOrderNumber, Invoice.PaymentTerms, Invoice.DueDate, Invoice.NetAmount, Invoice.TotalAmount, Invoice.Tax, Invoice.PaiementRCVD, Invoice.Overdue, Invoice.Month, Year([Invoice]![DueDate]), [Invoice]![month] & " - " & [Invoice]![Year]
HAVING (((Companies.Company) Like [Forms]![Invoice Listing]![ComboCompany]) AND ((Sales.Branch) Like [Forms]![Invoice Listing]![ComboBranch]))
ORDER BY Invoice.InvoiceDate DESC;

I would be very grateful if someone can help .

Phil
 

namliam

The Mailman - AWF VIP
Local time
Today, 08:28
Joined
Aug 11, 2003
Messages
11,696
First off, welcome to the forum :)

1) Dont use default names for your controls, this is asking for maintenance nightmares for in a year from now you will have no clue anymore what is combo957....

2) dont just splash sql on the forum, use the code tags (see my signature) for details
And please do some form of formatting to make it somewhat readable.

3)
While it isnt exactly best practice to apply logic like this in SQL.... It would be best to re-write the SQL in code before the execution of the query.... so that when all is selected you dont have a where clause at all....
So you would have lets say something along the lines of
Code:
mySQL = "Select ... " & _ 
       " From  .... " 
if Me.Combo957="ALL" then
    ' no where clause
else 
    mySQL = mySQL & " Where Invoice.Year = " & Me.Combo957
end if
mysql = mySQL & " Bla bla bla"

Currentdb.Querydefs("Yourqueryname").sql = mysql

4)
Regardless of that.....
Code:
WHERE ((([U]Invoice.Year[/U])=IIf([Forms]![Invoice Listing]![Combo957]="ALL",[B]([Invoice].[Year])<Year(Date())[/B],[U][Forms]![Invoice Listing]![Combo957][/U])))
How would you expect this to work? the underlined parts would equate to
Invoice.Year = [Forms]![Invoice Listing]![Combo957]
Which would be fine...
the bolded part for the ALL option would equate to
Invoice.Year=[Invoice].[Year]<Year(Date())
Which obviously breaks...

It is fixable, as long as you stick to this solution and only have one "option" that requires ALL it would seem (semi) doable. Though i will advice you to quickly go down the route I described @ 3 because it will save you a lot of headaches on problems like this. Not to mention it is far more efficient on the database
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:28
Joined
Feb 19, 2013
Messages
16,553
You can't use sql in this way for 2 reasons a) you are trying to compare a numeric value with a text value and b) you cannot build a WHERE statement this way in a query, not tested but try this

Code:
WHERE IIf(([Forms]![Invoice Listing]![Combo957]="ALL" AND [Invoice].[Year]<Year(Date())) OR ([Forms]![Invoice Listing]![Combo957]<>"ALL" AND [Invoice].[Year]=[Forms]![Invoice Listing]![Combo957]),True,False)=True

Why are you using distinctrow and group by, group by is usually only used when you are summing, counting etc which you are not doing.

It would also be more readable if you were to give your combobox a meaningful name
 

RainLover

VIP From a land downunder
Local time
Today, 18:28
Joined
Jan 5, 2009
Messages
5,041
Phil

Welcome to the Forum.

It looks to me like you are going a bit fast. Can you try writing the SQL that allows just the "ALL".

A little at a time is the best way to learn.

You have got two members who are very capable of helping you. Suggest you take note.

And next time don't wait two weeks. Just come here and ask for help.
 

PhilUp

Registered User.
Local time
Today, 00:28
Joined
Mar 4, 2015
Messages
60
Thank you guys and sorry if I made stupid. I had read previously in other posts that it would be best to include the SQL. So I just copied the SQL from the query.

Ok for the name of the combo. I did not change the name yet because I had started over so many times.


It also obvious to me that my SQL cannot be used as it is... it is not working !

I have also tried already without the Group by.

I agree with all of you, there are many mistakes and as I said I have tried soooo many different ways. I posted this one, but I could had posted other ways.... which also did not work.

If I replace the "ALL" by a year "2012" for example, it works.

Or if I remove the "ALL" and just keep the ([Invoice].[Year])<Year(Date()) it works also, but it is not what I want.

I want to be able to select just one year and also all years

Thank again and sorry for my ignorance.
Phil
 

PhilUp

Registered User.
Local time
Today, 00:28
Joined
Mar 4, 2015
Messages
60
Namliam,

Thank you so much. Your code works perfect.

THANK YOU so much, I really appreciate.
Phil
 

Users who are viewing this thread

Top Bottom