SQL problem within Access

K

kella101

Registered nightmare
Local time
Today, 06:00
Joined
Mar 20, 2008
Messages
58
Hi everyone,
Iv been working on this access program which i have inherited from my predecessor. here is the query (inlcuding the do until loop etc) which i have found i need to modify to do wat i want;

Do Until select_part_rst.EOF

num = num + 1
SysCmd acSysCmdUpdateMeter, num

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.use_fixed_price AS [Factor2], 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" & conversionRequired & ")) 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], " & ShipleyNorwalkStock & " As [Stock] " & _
" FROM part_select, part, price_list, price_factor, Custom2, CurrencyConUSD " & _
" 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] & _
" ORDER BY price_list.min_quantity DESC;"

select_part_rst.MoveNext

dbscurrent.Execute queryString2
Loop

What i need is for it to basically include a "if statement". Therefore, (I know this is completely incorrect but hopefully will get my point over), IF [Factor2] = '1' THEN price_factor.fixed_price_factor AS Factor ELSE price_factor.factor AS Factor. This IF statement would need to be ran repeatedly within the above SQL whenever the price_list or price_factor tables are used, thus to define whether to use e.g. price_list or fixed_price_list all depending on the figure within price_factor.use_fixed_price being a '1' or a '0'.

I have tried many different ways of doing this, and i am new to Access to struggling to get my head around it. Please could someone help!!! If there is no way to incorperate a IF statement in Access SQL, then how else could i do this? Thanks!!!
 
Help!

Hey could some one please help with the above problem, im not even sure if tis possible to do what im trying to!!
 
Simple Software Solutions

Hi, help at hand

in the design mode of your query you need to enter an Iff() statement.

IF [Factor2] = '1' THEN price_factor.fixed_price_factor AS Factor ELSE price_factor.factor AS Factor
Click to expand...

Under the column for Factor enter:

XFactor:Iff(Factor2='1',Price_factor.fixed_price_factor , price_factor.factor)
Click to expand...

Have changed it name to XFactor as you have a field called Factor in the price_factor table. This would cause confussion.

Also why have you got an ORDER BY in the query when you are only selecting one record at a time?

I can understand the reason for doing it one at a time (so that the progress bar will refresh) but do you not think that this is a little extravagent? Why not take out the SELECT TOP 1 command from the query as well as the

num = num + 1
SysCmd acSysCmdUpdateMeter, num
Click to expand...

Remove the Do Until Loop and simply put

DoCmd.Echo True, "Running Update query, please wait..."
Click to expand...

This will place the message on the status bar instead. You will find that the performace is greatly enhanced.

Just a suggestion though

CodeMaster::cool:http://www.icraftlimited.co.uk
 
Thanks...but...

hi, Thanks alot for the help, so it seems like it is possible what i am trying to do. There are a couple of bugs with this, which i will sort out (i inherited this code from a previous colleague and have only been working on it 2 days), but, firstly

where would i enter :

XFactor:Iff(Factor2='1',Price_factor.fixed_price_f actor , price_factor.factor)

you said in the query design, but its in the form design where the query is. and how would i integrate this into the query posted? This probably sounds stupid, but im normally a PHP programmer, just starting to use Access.

Thanks for the other stuff, i have now removed the ORDER by stuff etc. Cheers!
 
Simple Software Solutions

Kella,

Back now off my Easter break. Right, go into the design mode of your form and click on form properties. You will then see the property RecordSource. This is where the form is getting its data from. This will most likely be a query, click on the right of the item you will see a small button with 3 dots on, click on this. This will open up the underlying query.

Next you need to insert a column. Click on the top of any column and it should turn the column blue. When it does press the Insert Key to create a blank column.

Next type in the Iff() command from my previous post. Click on view datasheet mode to test the statement. Save the changes and reurn to the form design.

Finally in the control where you want to display the outcome, if required, point to the newly created field form your query.

Let me know how you get on.

David
 
Problem..

Hi thanks for your help, i hope you had a good easter holiday aswell.

My first problem is my form does not have a query within the "RawSource" or anything. The SQL query written for this is within the "code view" for the sub when the button gets hit. Therefore, when i goto the properties and the "..." page there is nothing there just a blank design view for a query.

Also, i attempted to create a query, simply on the "factor" or "factor2" columns of my "markup" table. however, it comes up with "the expression you entered has an invalid . (dot) or ! operator or invalid parenthesis..."

I entered the Xfactor... statement into the criteria field... i am unsure as to what i am doing here. Thanks again for your help!!!
 
Simple Software Solutions

Kella,

If you can send me a stripped down copy of your mdb with the offending items. This will enable me to recreate and rectify you issue. If data is sentative then send garbled data just as long as it is meaningful to the problem.

David
 
kella101 said:
where would i enter :

XFactor:Iff(Factor2='1',Price_factor.fixed_price_f actor , price_factor.factor)
Click to expand...
Seeing as the query is created in code, add it to the code...



Do Until select_part_rst.EOF

num = num + 1
SysCmd acSysCmdUpdateMeter, num

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.use_fixed_price AS [Factor2], price_factor.factor AS Factor, price_list.min_quantity AS [Price Break], part_select.quantity AS [Quantity Required], " & _
"Iff(Factor2='1',Price_factor.fixed_price_f actor , price_factor.factor) as [XFactor], " & _
" part.unit_id As [Unit],(price_factor.factor*(price_list.price" & conversionRequired & ")) AS Price," & _
" part_select.BOM,part_select.BOMName,part_select.de scription As [BOM Description],part_select.[BOM Seq] As [BOM Seq], " & _
" part_select.RS1,part_select.RS2,part_select.RS3,pa rt_select.info,part_select.Error, " & _
" part.lead_time As [Lead Time], " & ShipleyNorwalkStock & " As [Stock] " & _
" FROM part_select, part, price_list, price_factor, Custom2, CurrencyConUSD " & _
" WHERE part.id = part_select.part " & _
" AND price_list.part_id=part.id " & _
" AND Custom2.JDE_Price_Group=price_factor.customer_grou p " & _
" 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] & _
" ORDER BY price_list.min_quantity DESC;"

select_part_rst.MoveNext

dbscurrent.Execute queryString2
Loop

Because you are doing an insert into a table, you will need to amend the table to acomidate for the additional column as well.

This in my oppion is a BAD way of doing things tho... All this is doing is joining stuff 1 by 1 which is bound to be AWFULL slow.
Instead one big join can do the same and show a progress of only 1 query on the status bar by default.
 
Last edited:
Insert statement...

hi, the reason it does it this way is because it picks up information from the previous two inputted forms and runs queries on those inputs first, then runs another query depending on what company is selected from the latest form, what currency and what company base.

I tried inputting the Iff statements into the query,

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_factor.use_fixed_price AS [factor2], " & _
" Iff(factor2='1',fixed_price_list.price,price_list.price) AS [Base Price], " & _
" Iff(factor2='1',price_factor.fixed_price_factor,price_factor.factor) AS Factor, " & _
" Iff(factor2='1',fixed_price_list.min_quantity,price_list.min_quantity) AS [Price Break], " & _
" Iff(factor2='1',(price_factor.fixed_price_factor*(fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate)),(price_factor.factor*(price_list.price*CurrencyConUKP.sterling_exchange_rate))) AS Price, " & _
" part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],,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 Iff(factor2='1',fixed_price_list.part_id,price_list.part_id)=part.id " & _
" AND Custom2.JDE_Price_Group=price_factor.customer_group " & _
" AND price_factor.part_group=part." & PriceGroup & _
" AND Iff(factor2='1',fixed_price_list.nationality,price_list.nationality)= " & nationality & _
" AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
" AND part.id = " & "'" & select_part_rst!part & "'" & _
" AND Iff(factor2='1',fixed_price_list.min_qauntity,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] & _
";"

It comes up with a "Syntax error in INSERT INTO statement"

Please help, this is unchartered territory for myself.
 
Hi DCrake, the databases for this are ODBC and MYSQL ones held in other locations and by other programs, so i am unable to send you these, unless you know of a way. i could send you the program, but i doubt it would be very useful with out the data.
 
Try adding "debug.print queryString" before your execute.

Now you can find the actual query it is trying to execute in your immediate window.
Copy-paste this into an SQL window, run the query... now it should give a better error...

One thing tho try removing these spaces
" Iff(factor2='1',fixed_price_list.price,price_list.Xprice) AS [Base Price], " & _
" Iff(factor2='1',price_factor.fixed_price_factor,prXice_factor.factor) AS Factor, " & _
" Iff(factor2='1',fixed_price_list.min_quantity,pricXe_list.min_quantity) AS [Price Break], " & _
" Iff(factor2='1',(price_factor.fixed_price_factor*( fixed_price_list.price*CurrencyConUKP.sterling_excXhange_rate)),(price_factor.factor*(price_list.pricXe*CurrencyConUKP.sterling_exchange_rate))) AS Price, " & _

Marked by X

There are more spaces tho... remove them all and see if it helps
 
Hi, thanks for all the help btw,
The spaces aren't in my code, they appeared when i copied and pasted it into the reply window for some reason.

I put Debug.Print queryString2 befor my dbscurrent.Execute queryString2 and it just came up with the same "Syntax error in insert into statement"
 
Offcourse it will, but... now in the debug window (CTRL+G) you should find the SQL it is trying to execute.

Take this SQL past it into a query and execute it there.
This should (hopefully) give you a better error...

Another thing I am looking at... This "factor2" you actually mean the "factor2" that is created here??
price_factor.use_fixed_price AS [factor2],

If so you cannot use an alias as input for another column, you have to refer to the original column "price_factor.use_fixed_price" in this case.
 
This is what was in the debug window.

INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], Custom2.Customer_Name AS [Customer Name], part.id AS Part, price_factor.use_fixed_price AS [factor2], Iff(factor2='1',fixed_price_list.price,price_list.price) AS [Base Price], Iff(factor2='1',price_factor.fixed_price_factor,price_factor.factor) AS Factor, Iff(factor2='1',fixed_price_list.min_quantity,price_list.min_quantity) AS [Price Break], Iff(factor2='1',(price_factor.fixed_price_factor*(fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate)),(price_factor.factor*(price_list.price*CurrencyConUKP.sterling_exchange_rate))) AS Price, part_select.quantity AS [Quantity Required], part.unit_id As [Unit],,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, Curr
encyConUKP WHERE part.id = part_select.part AND Iff(factor2='1',fixed_price_list.part_id,price_list.part_id)=part.id AND Custom2.JDE_Price_Group=price_factor.customer_group AND price_factor.part_group=part.price_group AND Iff(factor2='1',fixed_price_list.nationality,price_list.nationality)= 823 AND Custom2.Customer_Name = 'Amcor Food Cans' AND part.id = '75493203' AND Iff(factor2='1',fixed_price_list.min_qauntity,price_list.min_quantity)<= part_select.quantity AND part_select.quantity = 1 AND part_select.BOM = '5002-018/15' AND part_select.[BOM Seq] = 0;

factor2 is the name of a column within the table markup. When this runs, it pulls out various information on parts and prices etc. and depending on what the value is for factor2 it should look in either the fixed price lists or the normal price lists.
so are you saying i cannot use the column variable within the iff statements? and that i should use the statement iff(price_factor.user_fixed_price='1',fixed_price_list.price,price_list.price) AS [Base Price} ??
 
What happenes if you paste above sql into a query window?

Access might give a more precise error.
 
If Factor2 is a column name in one of the tables that should be OK...

Code: 
INSERT INTO markup 
SELECT TOP 1 JDE_Customer_No AS [JDE Customer No]
, Custom2.Customer_Name AS [Customer Name]
, part.id AS Part
, price_factor.use_fixed_price AS [factor2]
, Iff(factor2='1',fixed_price_list.price,price_list. price) AS [Base Price]
, Iff(factor2='1',price_factor.fixed_price_factor,pr ice_factor.factor) AS Factor
, Iff(factor2='1',fixed_price_list.min_quantity,pric e_list.min_quantity) AS [Price Break]
, Iff(factor2='1',(price_factor.fixed_price_factor*( fixed_price_list.price*CurrencyConUKP.sterling_exchange_rate))
                 ,(price_factor.factor*(price_list.pric e*CurrencyConUKP.sterling_exchange_rate))) AS Price
, part_select.quantity AS [Quantity Required]
, part.unit_id As [Unit]
,
, 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   Iff(factor2='1',fixed_price_list.part_id,price_lis t.part_id)=part.id 
AND   Custom2.JDE_Price_Group=price_factor.customer_group 
AND   price_factor.part_group=part.price_group 
AND   Iff(factor2='1',fixed_price_list.nationality,price _list.nationality)= 823 
AND   Custom2.Customer_Name = 'Amcor Food Cans' 
AND   part.id = '75493203' 
AND   Iff(factor2='1',fixed_price_list.min_qauntity,price_list.min_quantity)<= part_select.quantity 
AND   part_select.quantity = 1 
AND   part_select.BOM = '5002-018/15' 
AND   part_select.[BOM Seq] = 0;
I took the trouble of spacing out your (pasted) sql... I think now the problem seems pretty clear doesnt it???

I removed all the "bad" spaces presuming they are not there in your code....

Overall I would say it might not be a bad idea if you make your code to look like above too, this is really much easier on the eyes and for maintenance.
 
Hey, I'v put the above into a new query, but it just comes up wit the same error.

I may have missed something really simple with the Iff statements because iv never written them befor in sql, is it actually possible what im trying to achieve? e.g. find this vaue for factor2 then due to wat this number is determine where to look for the other results and put them in the markup table?
 
hey sorry about the previous post, i hadnt refreshed my page so was replying to an old one, should i be able to enter that sql into the code for my form?
 
Nope... you still need to locate the problem and fix it... tho -to me- it seems pretty obvious now...

My point was, you maybe should make your code to look simular.... or more readable anyway. Because the problem seems to come from the unreadable-ness of your code.
 
Oh and there seems to be another problem....

In your query with the phase2='1' you are actually joining either Fixed_price_list or price_list, but never both.
While both are in the FROM clause, this will cause a "Cartagian product" (misspelled probably) and will mess up your results.

Whats more,
AND Iff(factor2='1',fixed_price_list.min_qauntity,price_list.min_quantity)<= part_select.quantity
AND part_select.quantity = 1
This seems to be "not ok"
Part_select.quantity=1
Something <= Part_select.quantity Therefor Something <= 1 ???
 
You must log in or register to reply here.

Similar threads

K
iif statements... then the WHERE clause? help!
Replies
5
Views
1,183
jzwp22
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom