Solved user input parameters for sql queries in access

[murray83]

Hi trying to pull just certain data using a SQL pass through query so the table isn't stored within the database but am able to query it and pull data

and when trying to pull data with this

SELECT 

Reference_Id,  Sku_Id, Code

FROM Inventory_Transaction

WHERE Code = 'Receipt' AND (((Reference_Id)=[Which Pre Advice]));

but it faults says missing right parenthesis

have tried google and found this site on stack over flow but its for dates and im wanting a string of 8 numbers inputted

https://stackoverflow.com/questions/13538305/how-to-prompt-user-input-parameters-for-sql-queries-in-access-2010

 

[theDBguy]

Hi. If you're using a passthrough query, then you'll have to modify its SQL statement to resolve the parameter before running/executing it. You can do this using a QueryDef object.

 

[murray83]

Hi. If you're using a passthrough query, then you'll have to modify its SQL statement to resolve the parameter before running/executing it. You can do this using a QueryDef object.

thanks will check that out ne chance of example

 

[theDBguy]

thanks will check that out ne chance of example

Try this one. Hope it helps...

 

[murray83]

well have come back to this and have made some progress but now get this

so the code works as just pure SQL, with the data hardcoded but i need/like to be able to input different SKU's

so in Pure SQL Code and Pure SQL Output is just the passthrough code working 100% ( a happy me )

but then after @theDBguy advice i checked out the link to allow user inputs and so have this code on the run query command button on my one form

Private Sub cmdJDAMoves_Click()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    "WHERE Sku_Id = " & [Forms]![Form1]![txtSKU]

    
  Set dbCurr = CurrentDb()
  Set qdfCurr = dbCurr.QueryDefs("Q_JDAMoves2")
  qdfCurr.SQL = strSQL



DoCmd.OpenQuery "Q_JDAMoves2"

End Sub

i have added the docmd.open as i think it should be there

but all it does is shown in pictures SQL Code 2 and says an error as shown in error pic, any help would be great

cheers one and all, and if your in the UK watch out for the windy wind

 

[theDBguy]

well have come back to this and have made some progress but now get this

so the code works as just pure SQL, with the data hardcoded but i need/like to be able to input different SKU's

so in Pure SQL Code and Pure SQL Output is just the passthrough code working 100% ( a happy me )

but then after @theDBguy advice i checked out the link to allow user inputs and so have this code on the run query command button on my one form

Private Sub cmdJDAMoves_Click()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    "WHERE Sku_Id = " & [Forms]![Form1]![txtSKU]

 
  Set dbCurr = CurrentDb()
  Set qdfCurr = dbCurr.QueryDefs("Q_JDAMoves2")
  qdfCurr.SQL = strSQL



DoCmd.OpenQuery "Q_JDAMoves2"

End Sub

i have added the docmd.open as i think it should be there

but all it does is shown in pictures SQL Code 2 and says an error as shown in error pic, any help would be great

cheers one and all, and if your in the UK watch out for the windy wind

Hi. I looked at your third image, and I noticed your words are bunched up together without spaces, and you didn't have a delimiter for your SKU value. Maybe try it this way?

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'"

Hope that helps...

 

[murray83]

Hi. I looked at your third image, and I noticed your words are bunched up together without spaces, and you didn't have a delimiter for your SKU value. Maybe try it this way?

strSQL = "SELECT Sku_Id, From_Loc_Id, Final_Loc_Id, Tag_Id, cast(Dstamp as date) as [Date] FROM Inventory_Transaction" & _
    " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'"

Hope that helps...

Ok that sort of helps but i have figured out whats wrong, but not show how to code it

when i click the button which then does all the SQL bits, it isn't putting "Date" but Date missing the speech marks and tried '"Date"' and then complained at me and also its missing off the ; at the end of the query

PS EDIT

OK i fixed the missing ; with another & ";"like so

 " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'" & ";"

 

[Gasman]

Date is a reserved word?

Use something else?

 

[theDBguy]

Ok that sort of helps but i have figured out whats wrong, but not show how to code it

when i click the button which then does all the SQL bits, it isn't putting "Date" but Date missing the speech marks and tried '"Date"' and then complained at me and also its missing off the ; at the end of the query

PS EDIT

OK i fixed the missing ; with another & ";"like so

 " WHERE Sku_Id = '" & [Forms]![Form1]![txtSKU] & "'" & ";"

So, are you all good now?

 

[murray83]

Date is a reserved word?

Use something else?

Love You

it worked