SQL problem within Access

Hi guys, eventually got everything here up and running. Thanks David, that union query was a great help, however, im still struggling with the IIf()'s, but will keep plugging at it.
 
Hi guys, i have done the modifications, as far as my basic knowledge makes me believe, correctly. i now receieve the error

"Query input must contain atleast one table or query."

Any ideas?
 
Simple Software Solutions

Kella

At what point are you getting this error.

Don't know if you are aware of this but if you go into your VBA code and set a breakpoint you can step through the code one line at a time by pressing the F8 key.

To set a breakpoint in your code where you want to start the trace then click on the left vertial strip on the line to begin. It should place a brown circle with a brown highlight across the nominated code. See pic

David
 

Attachments

  • Example.JPG
    Example.JPG
    48.3 KB · Views: 86
kella101 said:
Hi guys, i have done the modifications, as far as my basic knowledge makes me believe, correctly. i now receieve the error

"Query input must contain atleast one table or query."

Any ideas?
Click to expand...
Can you post the SQL of the query that is giving you this error?
 
Hi David, im getting this error when it runs the query... dbscurrent.Execute queryString2...
 
Hi Rabbie, here is my query... written within the Visual basic for the form which i use. I apologise for teh way the code is written, its just the easiest way to write it in the Vb but not the easiest to debug.

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_priced AS [Use_fixed], iif(Use_fixed = '1',QryPrices.FixedPrice,QryPrice.BasePrice) AS [Base_Price] " & _
" price_factor.fixed_price_factor AS Fixed_Factor, price_factor.factor AS [Factor], QryPrices.QuantityRequired AS [Price Break], part_select.quantity AS [Quantity Required], " & _
" part.unit_id As [Unit],iif(Use_fixed ='1',(price_factor.fixed_price_factor*(QryPrices.FixedPrice" & conversionRequired & ")),(price_factor.factor*(QryPrices.BasePrice" & 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, QryPrices, price_factor, Custom2, CurrencyConUSD " & _
" WHERE part.id = part_select.part " & _
" AND QryPrices.ID=part.id " & _
" AND Custom2.JDE_Price_Group=price_factor.customer_group " & _
" AND price_factor.part_group=part." & PriceGroup & _
" AND QryPrices.Nationality= " & nationality & _
" AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
" AND part.id = " & "'" & select_part_rst!part & "'" & _
" AND QryPrices.QuantityRequired <= 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 QryPrices.QuantityRequired DESC;"
 
Last edited:
Hi

I think you have a slight error in your syntax

Should be in this form

Code: 
INSERT INTO TAble ( Field1, Field2 )
SELECT TOP 1 Table1name.field1, Table1name.field2
FROM Table1name
ORDER BY Table1name.field1;

Hope this helps

Good luck

i
 
Hi the code works fine before i inputted the iif statements, and started using the QryPrices instead of the raw databases. so i dont think its anythin up with the format which it has been written.
 
kella101 said:
Hi the code works fine before i inputted the iif statements, and started using the QryPrices instead of the raw databases. so i dont think its anythin up with the format which it has been written.
Click to expand...
Well it's your code so it's up to to you. Since there no IIF in the query that is erroring I am not sure what they have to do with it.
 
hey there are at least 2 iif's in the query so not sure what you mean by

"Since there no IIf in the query"

??

The iffs are needed to either look at one number or look at another, depending on wat the value of "Use_fixed" is. The code is based on some which is already in use. which works perfectly fine. but i need to include these iifs etc. and thats when it stops working and comin up witht that error.
 
kella101 said:
hey there are at least 2 iif's in the query so not sure what you mean by

"Since there no IIf in the query"

??

The iffs are needed to either look at one number or look at another, depending on wat the value of "Use_fixed" is. The code is based on some which is already in use. which works perfectly fine. but i need to include these iifs etc. and thats when it stops working and comin up witht that error.
Click to expand...

My mistake. I looked through the query and managed to miss them both. My Apologies.
 
Simple Software Solutions

Kella,

Right what I want you to do is run the code again and when it comes up with the error click on debug. Then when it takes you to the VBA code you will see the code highlighted in yellow. If your immediate pane is not open press Ctrl+G, click into the pane and type in...

? QueryString2

and press return.

Highlight the results of the string and copy it (Ctrl+C)

Swith to the database window and select add new query

Don't select a table or query

Click on the SQL button on the toolbar you should see

SELECT;

Highligth this and press Ctrl+P (Paste).

Next switch to design view. All being well Access should repeat the error you got in the form. However, this time Access should give you some indication as to where the error lies in your code by positioning the cursor near the error. Fix the error and retry. If you then view in datasheet mode you can check the validity of your query.

Let me know how you get on.

David
 
I am gonna say this just ONCE more... :eek:

Make

Your

Freaking :eek:

Code

Freaking :eek:

Readable ! ! ! !

Now you find out yourself allready how hard it can be to debug *shit* like this. And you are just creating it.... So STOP appologizing and TAKE THE TIME to make your code READABLE ! ! !

Now that that rant is over I took the time to make your code readable...

Code: 
queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " 
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],  " 
queryString2 = queryString2 & " part.id AS Part,  " 
queryString2 = queryString2 & " price_factor.use_fixed_priced AS [Use_fixed],  " 
queryString2 = queryString2 & " iif(Use_fixed = '1',QryPrices.FixedPrice,QryPrice.BasePrice) AS [Base_Price]  " 
queryString2 = queryString2 & " price_factor.fixed_price_factor AS Fixed_Factor,  " 
queryString2 = queryString2 & " price_factor.factor AS [Factor],  " 
queryString2 = queryString2 & " QryPrices.QuantityRequired AS [Price Break],  " 
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required], " 
queryString2 = queryString2 & " part.unit_id As [Unit], " 
[COLOR="Red"]queryString2 = queryString2 & " iif(Use_fixed ='1' "
queryString2 = queryString2 & "   ,(price_factor.fixed_price_factor*(QryPrices.FixedPrice" & conversionRequired & ")) "
queryString2 = queryString2 & "   ,(price_factor.factor            *( QryPrices.BasePrice" & conversionRequired & ")) AS Price, " [/COLOR]
queryString2 = queryString2 & " part_select.BOM, " & _
queryString2 = queryString2 & " part_select.BOMName, " & _
queryString2 = queryString2 & " part_select.description As [BOM Description], " 
queryString2 = queryString2 & " part_select.[BOM Seq] As [BOM Seq],  " 
queryString2 = queryString2 & " part_select.RS1, " 
queryString2 = queryString2 & " part_select.RS2, " 
queryString2 = queryString2 & " part_select.RS3, " 
queryString2 = queryString2 & " part_select.info, " 
queryString2 = queryString2 & " part_select.Error,  " 
queryString2 = queryString2 & " part.lead_time As [Lead Time],  " 
[COLOR="red"]queryString2 = queryString2 & ShipleyNorwalkStock & " As [Stock]  " [/COLOR]
queryString2 = queryString2 & " FROM part_select, part, QryPrices, price_factor, Custom2, CurrencyConUSD " 
queryString2 = queryString2 & " WHERE part.id = part_select.part  " 
queryString2 = queryString2 & "   AND QryPrices.ID=part.id " 
queryString2 = queryString2 & "   AND Custom2.JDE_Price_Group=price_factor.customer_grou p " 
queryString2 = queryString2 & "   AND price_factor.part_group=part." & PriceGroup & _
queryString2 = queryString2 & "   AND QryPrices.Nationality= " & nationality & _
queryString2 = queryString2 & "   AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
queryString2 = queryString2 & "   AND part.id = " & "'" & select_part_rst!part & "'" & _
queryString2 = queryString2 & "   AND QryPrices.QuantityRequired <= part_select.quantity " & _
queryString2 = queryString2 & "   AND part_select.quantity = " & select_part_rst!quantity & _
queryString2 = queryString2 & "   AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & _
queryString2 = queryString2 & "   AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & _
queryString2 = queryString2 & " ORDER BY QryPrices.QuantityRequired DESC;"

There are two problems jumping out, marked in read.
The first problem is in the IIF there is missing a closing bracket ')' at the end of that IIF.

The second MAY not be a problem IF this is an numeric field. If it is a Character string you need to enclose that field with qoutes.

A couple, more genaral comments
1) this bit...
iif(Use_fixed ='1'
Use_Fixed is appearently a string, but you are stuffing numerice data in there... WHY? Numeric = Number, Character = String.

2) Prices
QryPrices.FixedPrice & conversionRequired
Both your fixedPrice as your BasePrice are concated with this ConversionRequired thing... However in your [Base_Price] Field you are not doing this. Which is inconsisten, tho it might be correct.
Also having two fields very simular (BasePrice and Base_Price) which are appearently very different.... Generaly NOT a good thing to do.

3) Insert
You are inserting into a table with SPACES in the column names!
Dont use spaces in column/table/any names in your database
 
Hey thanks, I have now changed my code to make it more readable!

I put in the new code, and now i come up with a type mismatch error, and i have found that it occurs in these lines;

queryString2 = queryString2 & " part_select.BOM, " & _
queryString2 = queryString2 & " part_select.BOMName, " & _
queryString2 = queryString2 & " part_select.description As [BOM Description], "

Also, with the lines u marked in red, what is wrong with;

queryString2 = queryString2 & ShipleyNorwalkStock & " As [Stock] "

Usually there should be a comma at the end of the line but this is the line before the FROM.

And i dont know the difference between "stuffing" in a number and a string... is this for example Number = 1, String = [String] ? or wat?

Use_fixed is a column name which should have a numerical value (either a 1 or a 0).

And i didnt create any of the tables or anythin, they wer already like that, and this thing is far too big to start messing about with the column names.

Here is all my code... in readable format lol

queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], "
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name], "
queryString2 = queryString2 & " part.id AS Part, "
queryString2 = queryString2 & " price_factor.use_fixed_priced AS Use_fixed, "
queryString2 = queryString2 & " iif(Use_fixed =1,QryPrices.FixedPrice,QryPrice.BasePrice) AS [Base_Price] "
queryString2 = queryString2 & " price_factor.fixed_price_factor AS Fixed_Factor, "
queryString2 = queryString2 & " price_factor.factor AS Factor, "
queryString2 = queryString2 & " QryPrices.QuantityRequired AS [Price Break], "
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required], "
queryString2 = queryString2 & " part.unit_id As [Unit], "
queryString2 = queryString2 & " iif(Use_fixed =1 "
queryString2 = queryString2 & " ,(price_factor.fixed_price_factor*(QryPrices.FixedPrice" & conversionRequired & ")) "
queryString2 = queryString2 & " ,(price_factor.factor *(QryPrices.BasePrice" & conversionRequired & "))) AS Price, "
queryString2 = queryString2 & " part_select.BOM, " & _
queryString2 = queryString2 & " part_select.BOMName, " & _
queryString2 = queryString2 & " part_select.description As [BOM Description], "
queryString2 = queryString2 & " part_select.[BOM Seq] As [BOM Seq], "
queryString2 = queryString2 & " part_select.RS1, "
queryString2 = queryString2 & " part_select.RS2, "
queryString2 = queryString2 & " part_select.RS3, "
queryString2 = queryString2 & " part_select.info, "
queryString2 = queryString2 & " part_select.Error, "
queryString2 = queryString2 & " part.lead_time As [Lead Time], "
queryString2 = queryString2 & ShipleyNorwalkStock & " As [Stock] "
queryString2 = queryString2 & " FROM part_select, part, QryPrices, price_factor, Custom2, CurrencyConUSD "
queryString2 = queryString2 & " WHERE part.id = part_select.part "
queryString2 = queryString2 & " AND QryPrices.ID=part.id "
queryString2 = queryString2 & " AND Custom2.JDE_Price_Group=price_factor.customer_group "
queryString2 = queryString2 & " AND price_factor.part_group=part." & PriceGroup & _
queryString2 = queryString2 & " AND QryPrices.Nationality= " & nationality & _
queryString2 = queryString2 & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" & _
queryString2 = queryString2 & " AND part.id = " & "'" & select_part_rst!part & "'" & _
queryString2 = queryString2 & " AND QryPrices.QuantityRequired <= part_select.quantity " & _
queryString2 = queryString2 & " AND part_select.quantity = " & select_part_rst!quantity & _
queryString2 = queryString2 & " AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" & _
queryString2 = queryString2 & " AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] & _
queryString2 = queryString2 & " ORDER BY QryPrices.QuantityRequired DESC;"
 
Last edited:
Here is all my code... in readable format lol
Click to expand...
But not as readable as Namliam made it. Try using the Code directive ie Code enclosed by [ and ] and ended with /CODE the same way. It really makes code much easier to read when your eyesight is NOT brilliant.
 
Last edited:
kella101 said:
Hey thanks, I have now changed my code to make it more readable!
Click to expand...
Easy copy paste huh??

n the new code, and now i come up with a type mismatch error, and i have found that it occurs in these lines;

queryString2 = queryString2 & " part_select.BOM, " & _
queryString2 = queryString2 & " part_select.BOMName, " & _
queryString2 = queryString2 & " part_select.description As [BOM Description], "
Click to expand...
:confused: It is your query... not mine, I didnt change anything (dont think so)
The problem is probably with the stray concatinations I left there by mistake.... There should NOT be any " & _ " at any of the lines anywere...

I have cleaned it up -I think-
Code: 
queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No], " 
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],  " 
queryString2 = queryString2 & " part.id AS Part,  " 
queryString2 = queryString2 & " price_factor.use_fixed_priced AS [Use_fixed],  " 
queryString2 = queryString2 & " iif(Use_fixed = '1',QryPrices.FixedPrice,QryPrice.BasePrice) AS [Base_Price]  " 
queryString2 = queryString2 & " price_factor.fixed_price_factor AS Fixed_Factor,  " 
queryString2 = queryString2 & " price_factor.factor AS [Factor],  " 
queryString2 = queryString2 & " QryPrices.QuantityRequired AS [Price Break],  " 
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required], " 
queryString2 = queryString2 & " part.unit_id As [Unit], " 
queryString2 = queryString2 & " iif(Use_fixed ='1' "
queryString2 = queryString2 & "   ,(price_factor.fixed_price_factor*(QryPrices.FixedPrice" & conversionRequired & ")) "
queryString2 = queryString2 & "   ,(price_factor.factor            *( QryPrices.BasePrice" & conversionRequired & ")) AS Price, " 
queryString2 = queryString2 & " part_select.BOM, " 
queryString2 = queryString2 & " part_select.BOMName, " 
queryString2 = queryString2 & " part_select.description As [BOM Description], " 
queryString2 = queryString2 & " part_select.[BOM Seq] As [BOM Seq],  " 
queryString2 = queryString2 & " part_select.RS1, " 
queryString2 = queryString2 & " part_select.RS2, " 
queryString2 = queryString2 & " part_select.RS3, " 
queryString2 = queryString2 & " part_select.info, " 
queryString2 = queryString2 & " part_select.Error,  " 
queryString2 = queryString2 & " part.lead_time As [Lead Time],  " 
queryString2 = queryString2 & ShipleyNorwalkStock & " As [Stock]  " 
queryString2 = queryString2 & " FROM part_select, part, QryPrices, price_factor, Custom2, CurrencyConUSD " 
queryString2 = queryString2 & " WHERE part.id = part_select.part  " 
queryString2 = queryString2 & "   AND QryPrices.ID=part.id " 
queryString2 = queryString2 & "   AND Custom2.JDE_Price_Group=price_factor.customer_group " 
queryString2 = queryString2 & "   AND price_factor.part_group=part." & PriceGroup 
queryString2 = queryString2 & "   AND QryPrices.Nationality= " & nationality 
queryString2 = queryString2 & "   AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'" 
queryString2 = queryString2 & "   AND part.id = " & "'" & select_part_rst!part & "'" 
queryString2 = queryString2 & "   AND QryPrices.QuantityRequired <= part_select.quantity " 
queryString2 = queryString2 & "   AND part_select.quantity = " & select_part_rst!quantity 
queryString2 = queryString2 & "   AND part_select.BOM = " & "'" & select_part_rst!BOM & "'" 
queryString2 = queryString2 & "   AND part_select.[BOM Seq] = " & select_part_rst![BOM Seq] 
queryString2 = queryString2 & " ORDER BY QryPrices.QuantityRequired DESC;"

Also, with the lines u marked in red, what is wrong with;

queryString2 = queryString2 & ShipleyNorwalkStock & " As [Stock] "

Usually there should be a comma at the end of the line but this is the line before the FROM.
Click to expand...
MY PREVIOUS POST said:
The second MAY not be a problem IF this is an numeric field. If it is a Character string you need to enclose that field with qoutes.
Click to expand...
It has nothing to do with the comma or anything it was just a remark saying IF it is a text field you have a problem...

ont know the difference between "stuffing" in a number and a string... is this for example Number = 1, String = [String] ? or wat?
Click to expand...
Stuffing, well... that is like "putting in".
You are putting in a 1 which is numeric .... But is is a string because it is using quotes '1'. It is not wrong parsee but it is not the best way to do it I think...
Use_Fixed is a column name which should have a numerical value (either a 1 or a 0).
Click to expand...
This is exactly what I am driving at. It is NOT numeric... the '1' makes it a string not a number. If Use_Fixed is a number it should just read 1 without the quotes!
If one is a number and the other a string, Access will have to do a so called "implicit conversion" meaning It has to make up a conversion for you. This can sometimes lead to errors, which we dont want offcourse.
Try and prevent as may implicit conversions as possible!

And i didnt create any of the tables or anythin, they wer already like that, and this thing is far too big to start messing about with the column names.
Click to expand...
I am not saying you should change it now, I am saying you shouldnt use it if you have the chance.... In an excisting app you are not going to be wanting to go back and change stuff like this.
 
Sorry i was talkin about the code directive and [ ] and /code thing what i didnt understand.
 
Because these directives are processded it is not easy to give a literal example of what I mean but I will try

if you have {code} but change the { to a [ and the } to ]
at the start of a block of code and at the end of the code put {/code} again changing the { etc the code will be formatted in your post.

This is only referring to when you post on this forum not when you put the code into Access.
 
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