Use value from form in query

[chromachem]

We have the following query

 
INSERT INTO tblTempSELECT *
FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], f7 AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26 AS [13C3-HFPO-DA], F32 AS [HFPO-DA], F38 AS PFHpA, F44 AS PFHxS, F50 AS ADONA, F56 AS PFOA, F62 AS PFNA, F68 AS PFOS, F74 AS [9Cl-PF3ONS], F80 AS [13C9-PFDA], F86 AS PFDA, F92 AS NMeFOSAA, F98 AS PFUnA, F104 AS [d5-NEtFOSAA], F110 AS NEtFOSAA, F116 AS [11Cl-PF3OUdS], F122 AS PFDoA, F128 AS PFTrDA, F134 AS PFTA FROM [TEXT;DATABASE=F:\PFAS Exported Data\;HDR=No].072823.csv WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt;

We have a form with a command button named cmdImport.



Is there a way to get the value from the form into the query to replace "072823" ?

If we want to import data from today we would enter 012224 in the form. the data file is 012224.csv.

Thanks

[Bob Fitz]

Can you post a copy of the db

[Welshgasman]

Concatenation?

[chromachem]

Pardon me for my lack of DB programming knowledge. My experience is excel VBA. Just learning SQL.

I am very familiar with concatenating in excel VBA. I don't have a clue how to concatenate an SQL statement using the value from a text box in a form.
I tried to find an example online to no avail. Most likely I didn't ask the question online correctly.

If anyone could give an example of how to do this type of concatenation of the value from a text box in a form, that would be great. I also see where I wasn't clear in my original post. The value is in the field called txtSequence. The query in my original posting is executed by clicking on the cmdImport command button. My aplologies for not being clearer.

[Welshgasman]

Try

Dim strSQL As String

strSQL = "INSERT INTO tblTempSELECT *
FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], f7 AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26 AS [13C3-HFPO-DA], F32 AS [HFPO-DA], F38 AS PFHpA, F44 AS PFHxS, F50 AS ADONA, F56 AS PFOA, F62 AS PFNA, F68 AS PFOS, F74 AS [9Cl-PF3ONS], F80 AS [13C9-PFDA], F86 AS PFDA, F92 AS NMeFOSAA, F98 AS PFUnA, F104 AS [d5-NEtFOSAA], F110 AS NEtFOSAA, F116 AS [11Cl-PF3OUdS], F122 AS PFDoA, F128 AS PFTrDA, F134 AS PFTA FROM [TEXT;DATABASE=F:\PFAS Exported Data\;HDR=No]." & Forms!YourForm!YourControl & ".csv WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt";

Debug.Print strSQL

That way you can always check your syntax and when correct use that in the Execute command.

If the code is in your actual form you can just use Me.YourControl where that is the name of your control with your value.

[Micron]

I am very familiar with concatenating in excel VBA. I don't have a clue how to concatenate an SQL statement using the value from a text box in a form.

The rules are the same. The tricky point will be using the correct control reference. As WGM suggests, if the code is in the form you can use something like

" ... f7 AS [Date], " & Me.ControlNameHere & " AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA],..."

The suggestion to debug.print the sql is a good one since you will need to ensure that strings are delimited (' or "") as well as dates (#) but no delimiting for values that are of type Number (not the same as Numeric). Other points regarding getting a reference to the form control:
- the form has to be open or
- you have to pass values to another form/report when it opens
- if the control is a combo, the returned value is often the record id and is not what you need
- it is a bit tricky to refer to combo columns in a query

EDIT - date is a poor choice for a field name.

[chromachem]

OK. Getting there I think.

The form name is frmImport537Data
The unbound text box is txtSequence. Today the value we would enter is 012324. To me this is a numeric value - we need the leading Zero.
There is a command box on the form (cmdImport) that we click after entering a value used to create the name for the import file.

We have built an event for the cmdImport command button as follows:

Private Sub cmdImport_Click()

Dim strSQL As String




strSQL = "INSERT INTO tblTempSELECT * FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], f7 AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26 AS [13C3-HFPO-DA], F32 AS [HFPO-DA], F38 AS PFHpA, F44 AS PFHxS, F50 AS ADONA, F56 AS PFOA, F62 AS PFNA, F68 AS PFOS, F74 AS [9Cl-PF3ONS], F80 AS [13C9-PFDA], F86 AS PFDA, F92 AS NMeFOSAA, F98 AS PFUnA, F104 AS [d5-NEtFOSAA], F110 AS NEtFOSAA, F116 AS [11Cl-PF3OUdS], F122 AS PFDoA, F128 AS PFTrDA, F134 AS PFTA FROM [TEXT;DATABASE=F:\PFAS Exported Data\;HDR=No]." & Forms!frmImport537Data!txtSequence & ".csv WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt"


Debug.Print strSQL


'DoCmd.OpenQuery "qryStructure"
DoCmd.OpenQuery "qryAppend"
DoCmd.OpenQuery "qry537Upsert"
   


End Sub

Is there a problem with the fact that txtSequence contains a numeric value? I'm guessing I have the correct string stored in strSQL. Our entire lab operation is built around using the mmddyy format to name sequences. Its way too late to change that.

Query qryAppend originally contained the sql in the original post.

Now that strSQL hopefully contains the string I need, what do I put in qryAppend? Do I somehow run the query where I performed the concatenation (cmdImport_Click() )?

As always, any help is greatly appreciated.

[Micron]

Does your .Print look right? Did you copy and paste into a new query and try to switch to Datasheet view? What happened/happens?
Also,
- an unbound textbox may not have a value. Can happen if it has something in it but focus never leaves the control. You may have to use its .Text property instead of the default .Value property.
- add Debug.Print TypeName(Forms!frmImport537Data!txtSequence.Text) and
- add Debug.Print TypeName(Forms!frmImport537Data!txtSequence.Value)
and check the outputs. 1234 is numeric but it can be number or text data type. If it's text, you need to add text delimiters to your concatenation. If you get no printout for .Value then it proves my point re unbound textbox, in which case you'd be passing Null or zls to the query sql.

[pbaldy]

Now that strSQL hopefully contains the string I need, what do I put in qryAppend? Do I somehow run the query where I performed the concatenation (cmdImport_Click() )?

A couple of ways, most commonly

CurrentDb.Execute strSQL, dbFailOnError

That would be in place of the OpenQuery line(s).

You wouldn't want delimiters around the value as it's being used for a file name, not data. Focus shouldn't be an issue here; since the code is behind a button focus will have left the textbox.

[CJ_London]

Today the value we would enter is 012324. To me this is a numeric value - we need the leading Zero

numbers do not have leading zeros, if they do, then they have to be text.

You can use the format property to display leading zeros (simply "000000") for numbers, but the underlying value remains numeric. This is not an access issue, it is the way it is in the world. Open excel and type 012324 in a cell, see what happens - the leading zero is stripped off. Still in excel put a single quote before entering your number i.e. '012324. That retains the leading zero but with a warning 'number stored as text'.

[Micron]

numbers do not have leading zeros, if they do, then they have to be text.

Agreed, hence my suggestions. People often don't know the difference between data types where numeric characters are concerned. I was hoping to dispel any doubt about what is posted and what the source is since they are not always the same despite what is claimed.

[chromachem]

strSQL = "INSERT INTO tblTemp SELECT * FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], f7 AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26 AS [13C3-HFPO-DA], F32 AS [HFPO-DA], F38 AS PFHpA, F44 AS PFHxS, F50 AS ADONA, F56 AS PFOA, F62 AS PFNA, F68 AS PFOS, F74 AS [9Cl-PF3ONS], F80 AS [13C9-PFDA], F86 AS PFDA, F92 AS NMeFOSAA, F98 AS PFUnA, F104 AS [d5-NEtFOSAA], F110 AS NEtFOSAA, F116 AS [11Cl-PF3OUdS], F122 AS PFDoA, F128 AS PFTrDA, F134 AS PFTA FROM [TEXT;DATABASE=F:\PFAS Exported Data\;HDR=No]." & Forms!frmImport537Data!txtSequence & ".csv WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt;"

[chromachem]

Got too excited a moment ago and posted without context.

THANK YOU to all who helped!!!! So much appreciated. I had to move the semi colon at the end of the statement and we are good to go!!! Here is the final strSQL concatenation

strSQL = "INSERT INTO tblTemp SELECT * FROM (SELECT Replace(f4,'.d','') AS LabID, F6 AS [Level], f7 AS [Date], F8 AS PFBS, F11 AS [Symmetry PFBS], F14 AS [13C6-PFHxA], F20 AS PFHxA, F23 AS [Symmetry PFHxS], F26 AS [13C3-HFPO-DA], F32 AS [HFPO-DA], F38 AS PFHpA, F44 AS PFHxS, F50 AS ADONA, F56 AS PFOA, F62 AS PFNA, F68 AS PFOS, F74 AS [9Cl-PF3ONS], F80 AS [13C9-PFDA], F86 AS PFDA, F92 AS NMeFOSAA, F98 AS PFUnA, F104 AS [d5-NEtFOSAA], F110 AS NEtFOSAA, F116 AS [11Cl-PF3OUdS], F122 AS PFDoA, F128 AS PFTrDA, F134 AS PFTA FROM [TEXT;DATABASE=F:\PFAS Exported Data\;HDR=No]." & Forms!frmImport537Data!txtSequence & ".csv WHERE F4 Not Like 'Blank*' And F4<>'Data File')  AS txt;"

Then used Baldy's code to execute from within the build event of the text box in the form.

CurrentDb.Execute strSQL, dbFailOnError

Slick as can be!!!!!!

Thanks again to all.

[Micron]

FYI, pretty sure that if you run a sql statement from vba, you don't have to use the trailing ; .

[Welshgasman]

I never use them.