SQL problem within Access

[kella101]

The last bit is for the min quantity, therefore, it only shows results for the part which has more than the minimum quantity in stock.
e.g.
price_list.min_quantity (this value shows the minimum quantity allowed in stock)
part_select.quantity (this shows the quantity in stock),
therefore if the min quantity is bigger than the min quantity in stock, the results will not be shown as they are required to be restocked. so this is ok.

Also for the "Cartagian product", am i able to put an iff into the From clause?

Also, i have tried to write my sql like you showed above, however, it does not allow to to do that many " & _ " join lines within the code of a form for one statement.

 

[kella101]

Hi i now have the error " undefined function Iff in expression"

 

[kella101]

I also read somewhere that it was "IIF not IFF" so changed them all to that and got a different error - "Too few parameters, expected 6"
please help!!!

 

[namliam]

Also for the "Cartagian product", am i able to put an iff into the From clause?

No you cannot, you are in codes anyway. You have to make seperate queries for that... otherwize you get the pain...

Also, i have tried to write my sql like you showed above, however, it does not allow to to do that many " & _ " join lines within the code of a form for one statement.

A good way IMHO to make sql like above....
Variable = variable & "Select bla bla bla "
Variable = variable & "bla bla bla "
Variable = variable & "bla bla bla "
Variable = variable & "bla bla bla "
Variable = variable & "bla bla bla "
Variable = variable & "bla bla bla "

No need for continuations...

I also read somewhere that it was "IIF not IFF"

I totaly missed the IFF, things the mind makes you see

"Too few parameters, expected 6"
please help!!!

I am helping arent I ?? And at no charge as well! *Geez*

The parameters error is saying it cannot get the data from the tables.... Make sure you didnt make any typos and stuff... if all else fails (again)
Get the sql paste it into a query and find out what parameters it is asking for...

 

[DCrake]

Simple Software Solutions

Kella

David here,

You are right it stands for Immediate If

Your too few parmenters errors relates to fields it was expecting and not finding. Does the field Factor2 really exist in the database as a field or not?

If not then this is why it is coming up with this error as it is referenced 6 times in your query.

What we need to do now is a little bit of detective work by the means or process elimination.

Move all you IIF statements out of the query using cut and paste and rem them out. Next test the query for any errors. If none found then start by adding one iff statement at a time until you find out what is causing the error. A bit long winded, I know, but you will learn alot about the issue by doing it this way.


David

 

[kella101]

hey mailman, yes you are helping, thanks alot!! it seemed everything had gone a bit quiet on me so got a bit frustrated, but i am very grateful for your help!!

so would the too few parameters mean it cant pull the data from the original database, in order to put the data in markup, or that there is something missing in markup?

Also David, thank you for your help aswell!! i will begin testing each IIF statement to see if there is one troublesome one. but factor2 does exist as a column in my markup table, and factor2 is referenced 7 times.

i know this seems like a long way of doing it, but it could run the query into another table e.g. temp, and put all the factor2's into there, then just b4 the loop again for the next part, it could check the value of factor2, then if it is a 1 run the sql query for fixed prices and input the stuff into markup, but if the value of factor2 for this part is 0 then run the sql for the normal prices and input the results into markup. i dunno was just an idea, which is very long winded and probably slow. just seemed alot simpler to use the iif but im struggling alot as you guys can probably tell.

 

[namliam]

Markup is your destination table tho... Factor2 needs to be available in one of the tables in you from clause
FROM part_select
, part
, fixed_price_list
, price_factor
, Custom2
, CurrencyConUKP

If it is not, then that is the guilty party.

The 'proper' way to solve the Factor2 problem is to make a union I think...
Select...
From ...
Where Factor2 = 1
Union
Select...
From ...
Where Factor2 = 0

One of the 2 queries will return NO records and the other will return the proper ones.... allways... if I am to understand the problem correctly.

it seemed everything had gone a bit quiet on me

There are other problems in this world than only yours. If you want help on the dot on the spot, I can do that too. But I would have to charge you for a round trip to leeds + a fee per hour

I still say, copy/paste the SQL into a query and find out what parameters it is looking for.

 

[DCrake]

Simple Software Solutions

I say

There are other problems in this world than only yours. If you want help on the dot on the spot, I can do that too. But I would have to charge you for a round trip to leeds + a fee per hour

Is a bit of a holiday isn' it, I mean from the Netherlands Why not get someone local if you are going down that route. Burnley is only a spit and a stride from Leeds, isn't it Kella?

david:http://www.icraftlimited.co.uk

 

[DCrake]

Simple Software Solutions

Do me a favour Kella

Do a screen dump of the design of your query showing the tables you have in the query. It seems you are referencing file in tables that don't exist as you do not seem to have any joins in there

Also if you can send me the table structures of the applicable tables in a blank mdb then I can attempt to recreate the query from my end.

David

 

[kella101]

Hi, the tables i am referencing are there, because everything work great without the IIF in there. but then it is only using either the fixed prices or the normal costs (depending on which is hard coded into the sql), also the tables are not mdb, there are a variety of databases from ODBC to MYSQL databases which are accessed via universe. therefore im not sure how i would send you these tables.

these are my two sql statements which work eactly right when hard coded in;

for normal prices -

queryString2 = " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " & _
" Custom2.Customer_Name AS [Customer Name], part.id AS Part, price_list.price AS [Base Price], " & _
" price_factor.factor AS Factor, price_list.min_quantity AS [Price Break], part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],(price_factor.factor*(price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price,part_select.BOM," & _
" part_select.BOMName,part_select.description As [BOM Description],part_select.[BOM Seq] As [BOM Seq]," & _
" part_select.RS1,part_select.RS2,part_select.RS3,part_select.info,part_select.Error, " & _
" part.lead_time As [Lead Time],part.uk_stock As [Stock] " & _
" FROM part_select, part, price_list, price_factor, Custom2, CurrencyConUKP " & _
" WHERE part.id = part_select.part " & _
" AND price_list.part_id=part.id " & _
" AND Custom2.JDE_Price_Group=price_factor.customer_group " & _
" AND price_factor.part_group=part." & PriceGroup & _
" AND price_list.nationality= " & nationality & _
" AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
" AND part.id = " & "'" & select_part_rst!part & "'" & _
" AND price_list.min_quantity <= part_select.quantity " & _
" AND part_select.quantity = " & select_part_rst!quantity & _
" AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & _
" AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & ";"

for fixed prices -

queryString2 = " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " & _
" Custom2.Customer_Name AS [Customer Name], part.id AS Part, fixed_price_list.price AS [Base Price], " & _
" price_factor.fixed_price_factor AS Factor, fixed_price_list.min_quantity AS [Price Break], part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],(price_factor.fixed_price_factor*(fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price,part_select.BOM," & _
" part_select.BOMName,part_select.description As [BOM Description],part_select.[BOM Seq] As [BOM Seq]," & _
" part_select.RS1,part_select.RS2,part_select.RS3,part_select.info,part_select.Error, " & _
" part.lead_time As [Lead Time],part.uk_stock As [Stock] " & _
" FROM part_select, part, fixed_price_list, price_factor, Custom2, CurrencyConUKP " & _
" WHERE part.id = part_select.part " & _
" AND fixed_price_list.part_id=part.id " & _
" AND Custom2.JDE_Price_Group=price_factor.customer_group " & _
" AND price_factor.part_group=part." & PriceGroup & _
" AND fixed_price_list.nationality= " & nationality & _
" AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
" AND part.id = " & "'" & select_part_rst!part & "'" & _
" AND fixed_price_list.min_quantity <= part_select.quantity " & _
" AND part_select.quantity = " & select_part_rst!quantity & _
" AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & _
" AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & ";"

And what i want these to be basically combined, so it will get some information from "price_factor.use_fixed_price" and if the value in here, for this part is '1' then it will use the query for the fixed price, but if its '0' then it will use the sql for normal prices.

hope you's understand. is there anythin i can give yous to help yous help me? lol thanks!

 

[namliam]

Exactly now again with the unreadable queries... at least the problem doesnt excist here (two comma's after [unit])

Now take your 2 queries that work. Store one into a variable qryFixed, the other into qryNormal.

The query you need to execute is:
qryFixed & _
" Union " & _
qryNormal

Again I really really urge you for all that is holy to developers please re-write that SQL into something readable

 

[DCrake]

Simple Software Solutions

Kella

Now I see your problem

In your normal prices query your are referencing the follwing tables:

" FROM part_select, part, price_list, price_factor, Custom2, CurrencyConUKP "

In your Fixed prices query your are referencing the follwing tables:

" FROM part_select, part, fixed_price_list, price_factor, Custom2, CurrencyConUKP "

As ou are trying to combine the two to incorporate the Iff() command you will need to include both the fixed_price_list table and the price_list table into your query.

This is why you are getting the too few parameters error.

Within your Do Until Loop I would suggest that you involve an If Then Else statement to determine which SQL to use based on the current record being processed. Once found then use the If statement to toggle which query sql to use.

This will eliminate the need for your Iff() condition.

David.

 

[kella101]

Hi David,
Yea i have tried to include both, if i put both in it says too many paramters expected 2, then if i take one out, it says too few parameters expected 6?

I tried to include a VB if statement, then run one query or the other, however i struggled to get the code to recognise the variable which will have been found using sql code, then used in the if statement in the VB code.

does that make sense?

Kella..

 

[kella101]

hi mail man,
I attempted writing the "Union" for the execute of the query, and it simply came up with "characters found after query"... So i tried to write the union so the statement was one large statement including both queries, and unioned these. but it simply came up with "missing operator" :S im not sure how i am meant to write this union into my code, as if union is an sql function, then it will have to be incorperated into the query variable definition. e.g.
qry1 = "Insert into....." & _
"UNION" & _
"Insert into....." & _

dbscurrent.execute qry1

so im a little confused as to how you mean mailman...

 

[namliam]

You cannot do 2 inserts in a union... The correct syntax is something like...

Insert into table
select ... from ... where ...
union
select ... from ... where ...

Union indeed is an SQL operator and a quick google on Union SQL would find it for you, that way you can find more details like:
What is the difference between "Union" and "Union all" ?

 

[kella101]

Now it says missing parameter, here is the sql below, i know you do not like this view but this is how you write sql within the access code, and it only allows you so many "& _ " so have to try and fit them all in....

qryfixed = " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " & _
" Custom2.Customer_Name AS [Customer Name], part.id AS Part, fixed_price_list.price AS [Base Price], " & _
" price_factor.fixed_price_factor AS Factor, fixed_price_list.min_quantity AS [Price Break], part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],(price_factor.fixed_price_factor*(fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price,part_select.BOM," & _
" part_select.BOMName,part_select.description As [BOM Description],part_select.[BOM Seq] As [BOM Seq]," & _
" part_select.RS1,part_select.RS2,part_select.RS3,part_select.info,part_select.Error, " & _
" part.lead_time As [Lead Time],part.uk_stock As [Stock] " & _
" FROM part_select, part, fixed_price_list, price_factor, Custom2, CurrencyConUKP " & _
" WHERE part.id = part_select.part AND fixed_price_list.part_id=part.id AND Custom2.JDE_Price_Group=price_factor.customer_group AND price_factor.part_group=part." & PriceGroup & _
" AND fixed_price_list.nationality= " & nationality & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
" AND part.id = " & "'" & select_part_rst!part & "'" & " AND fixed_price_list.min_quantity <= part_select.quantity AND part_select.quantity = " & select_part_rst!quantity & _
" AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & " AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & _
" UNION " & _
" SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " & _
" Custom2.Customer_Name AS [Customer Name], part.id AS Part, price_list.price AS [Base Price], " & _
" price_factor.factor AS Factor, price_list.min_quantity AS [Price Break], part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],(price_factor.factor*(price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price,part_select.BOM," & _
" part_select.BOMName,part_select.description As [BOM Description],part_select.[BOM Seq] As [BOM Seq]," & _
" part_select.RS1,part_select.RS2,part_select.RS3,part_select.info,part_select.Error, " & _
" part.lead_time As [Lead Time],part.uk_stock As [Stock] " & _
" FROM part_select, part, price_list, price_factor, Custom2, CurrencyConUKP " & _
" WHERE part.id = part_select.part AND price_list.part_id=part.id AND Custom2.JDE_Price_Group=price_factor.customer_group AND price_factor.part_group=part." & PriceGroup & _
" AND price_list.nationality= " & nationality & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & " AND part.id = " & "'" & select_part_rst!part & "'" & _
" AND price_list.min_quantity <= part_select.quantity AND part_select.quantity = " & select_part_rst!quantity & " AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & _
" AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & ";"

 

[namliam]

i know you do not like this view but this is how you write sql within the access code, and it only allows you so many "& _ " so have to try and fit them all in....

So dont use " & _ " and do it like I suggested earlier... like so:

qryFixed = "" 
qryfixed = qryFixed & "INSERT INTO markup "
qryfixed = qryFixed & "SELECT TOP 1  "
qryfixed = qryFixed & "       JDE_Customer_No AS [JDE Customer No],   "
qryfixed = qryFixed & "       Custom2.Customer_Name AS [Customer Name],  "
qryfixed = qryFixed & "       part.id AS Part,  "
qryfixed = qryFixed & "       fixed_price_list.price AS [Base Price],  "
qryfixed = qryFixed & "       price_factor.fixed_price_factor AS Factor,   "
qryfixed = qryFixed & "       fixed_price_list.min_quantity AS [Price Break],   "
qryfixed = qryFixed & "       part_select.quantity AS [Quantity Required],  "
qryfixed = qryFixed & "       part.unit_id As [Unit],  "
qryfixed = qryFixed & "       (price_factor.fixed_price_factor*(fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price, "
qryfixed = qryFixed & "       part_select.BOM,  "
qryfixed = qryFixed & "       part_select.BOMName,  "
qryfixed = qryFixed & "       part_select.description As [BOM Description],  "
qryfixed = qryFixed & "       part_select.[BOM Seq] As [BOM Seq],  "
qryfixed = qryFixed & "       part_select.RS1,  "
qryfixed = qryFixed & "       part_select.RS2,  "
qryfixed = qryFixed & "       part_select.RS3,  "
qryfixed = qryFixed & "       part_select.info,  "
qryfixed = qryFixed & "       part_select.Error,   "
qryfixed = qryFixed & "       part.lead_time As [Lead Time],  "
qryfixed = qryFixed & "       part.uk_stock As [Stock]   "
qryfixed = qryFixed & "FROM   part_select,   "
qryfixed = qryFixed & "       part,   "
qryfixed = qryFixed & "       fixed_price_list,   "
qryfixed = qryFixed & "       price_factor,   "
qryfixed = qryFixed & "       Custom2,   "
qryfixed = qryFixed & "       CurrencyConUKP " 
qryfixed = qryFixed & "WHERE  part.id = part_select.part   "
qryfixed = qryFixed & "  AND  fixed_price_list.part_id=part.id  "
qryfixed = qryFixed & "  AND  Custom2.JDE_Price_Group=price_factor.customer_group  "
qryfixed = qryFixed & "  AND  price_factor.part_group=part." & PriceGroup 
qryfixed = qryFixed & "  AND  fixed_price_list.nationality= " & nationality  
qryfixed = qryFixed & "  AND  Custom2.Customer_Name = '" & CustomerSelection & "' "
qryfixed = qryFixed & "  AND  part.id = '" & select_part_rst!part & "' "
qryfixed = qryFixed & "  AND  fixed_price_list.min_quantity <= part_select.quantity  "
qryfixed = qryFixed & "  AND  part_select.quantity = " & select_part_rst!quantity 
qryfixed = qryFixed & "  AND  part_select.BOM = '" & select_part_rst!BOM & "' "
qryfixed = qryFixed & "  AND  part_select.[BOM Seq] = " & select_part_rst![BOM Seq] 
qryfixed = qryFixed & " UNION "
qryfixed = qryFixed & "SELECT TOP 1  "
qryfixed = qryFixed & "       JDE_Customer_No AS [JDE Customer No], "
qryfixed = qryFixed & "       Custom2.Customer_Name AS [Customer Name],  "
qryfixed = qryFixed & "       part.id AS Part,  "
qryfixed = qryFixed & "       price_list.price AS [Base Price],  "
qryfixed = qryFixed & "       price_factor.factor AS Factor,  "
qryfixed = qryFixed & "       price_list.min_quantity AS [Price Break],  "
qryfixed = qryFixed & "       part_select.quantity AS [Quantity Required], "
qryfixed = qryFixed & "       part.unit_id As [Unit], "
qryfixed = qryFixed & "      (price_factor.factor*(price_list.price*CurrencyConUKP.sterling_exchange_rate)) AS Price, "
qryfixed = qryFixed & "       part_select.BOM, "
qryfixed = qryFixed & "       part_select.BOMName, "
qryfixed = qryFixed & "       part_select.description As [BOM Description], "
qryfixed = qryFixed & "       part_select.[BOM Seq] As [BOM Seq], "
qryfixed = qryFixed & "       part_select.RS1, "
qryfixed = qryFixed & "       part_select.RS2, "
qryfixed = qryFixed & "       part_select.RS3, "
qryfixed = qryFixed & "       part_select.info, "
qryfixed = qryFixed & "       part_select.Error,  "
qryfixed = qryFixed & "       part.lead_time As [Lead Time], "
qryfixed = qryFixed & "       part.uk_stock As [Stock] " 
qryfixed = qryFixed & "FROM   part_select,  "
qryfixed = qryFixed & "       part,  "
qryfixed = qryFixed & "       price_list,  "
qryfixed = qryFixed & "       price_factor,  "
qryfixed = qryFixed & "       Custom2,  "
qryfixed = qryFixed & "       CurrencyConUKP "
qryfixed = qryFixed & "WHERE  part.id = part_select.part  "
qryfixed = qryFixed & "  AND  price_list.part_id=part.id  "
qryfixed = qryFixed & "  AND  Custom2.JDE_Price_Group=price_factor.customer_group "
qryfixed = qryFixed & "  AND  price_factor.part_group=part." & PriceGroup 
qryfixed = qryFixed & "  AND  price_list.nationality= " & nationality  
qryfixed = qryFixed & "  AND  Custom2.Customer_Name = '" & CustomerSelection & "' "
qryfixed = qryFixed & "  AND  part.id = '" & select_part_rst!part & "' "
qryfixed = qryFixed & "  AND  price_list.min_quantity <= part_select.quantity  "
qryfixed = qryFixed & "  AND  part_select.quantity = " & select_part_rst!quantity  "
qryfixed = qryFixed & "  AND  part_select.BOM = '" & select_part_rst!BOM & "' "
qryfixed = qryFixed & "  AND  part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & ";"

Or something like that... There is nothing limiting you from doing i.e.

qry1Select = ....
qry1from = ....
qry1where = ....
qry2Select = ....
etc...

Eitherway, make your sql readable and supportable...

What happens if you use debug.print to find out what code it is trying to execute? Then taking this code and pasting it into a query.... it will ask you for the parameters and you will find out what is wrong!

Regards

 

[kella101]

hey guys... i have tried debugging it, and putting it in a query window, and it comes up wit the same error -
"Syntax error - (missing operator) in query expression 'part.id = part_select.part
AND fixed_price_list.part_id=part.id
AND Custom2.JDE_Price_Group=price_factor.customer_group
AND price_factor.part_group = part.prie_group
AND ficed_price_list.nationality= 823
AND Custom2.Customer_Name = 'A'.

That is the entire error message i recieve...!??!?!

 

[namliam]

Looks like the space in "price_factor.customer_grou p" is still there?? Tho you said it wasnt??

That space will trigger the error!

 

[kella101]

Hey, that space was put in by this quick reply box at the bottom, and it not in the code... each section of the code works fine on its own (i.e. the code for fixed prices works perfectly for fixed, and the normal works fine), but when they are combined for this union they do not work...

I put the query in the query window, sort of got it workin, by inputting the variables manually when it asked, and it came up with two results... but i only want it to come up with 1, that is the purpose, to choose one of the two. not them both. ??