Solved refer to a number field in a sql statement

[slharman1]

Hi. Thanks for letting me know what you think. I'll try to make it even better next time. Good luck with your learning journey. Cheers!

What? No leads to more good learning?

 

[theDBguy]

What? No leads to more good learning?

Not sure what else you specifically needed. You can use the Search bar on the blog page to enter any topic you're interested to read about; and hopefully, there's an article to cover it. I also have some (not much) stuff on my website. Maybe you can check it out as well and let me know what you think. Otherwise, if you have a specific topic you need to learn, please let us know, and we'll point you to some resources on that topic. Cheers!

 

[slharman1]

Not sure what else you specifically needed. You can use the Search bar on the blog page to enter any topic you're interested to read about; and hopefully, there's an article to cover it. I also have some (not much) stuff on my website. Maybe you can check it out as well and let me know what you think. Otherwise, if you have a specific topic you need to learn, please let us know, and we'll point you to some resources on that topic. Cheers!

Is the code repository forum on this site safe to download code and use it in my application?

 

[Isaac]

That's all over my head, where can I go to educate my self to read the code you posted and understand what I read?

no idea if this will help at all.

think of a sql statement, in its literal sense, like:

select column1 from table1 where column2 = "text"

Your job in VBA is to generate that literal string value

So you cannot go:

declare variable
assign variable a value
try to execute: select column1 from table2 where column2 = variablename

because, "variablename" isn't what the LITERAL sql statement would be, right?

you have to do:

declare variable (original)
also declare a string variable (we're adding this now)
assign original variable a value
now assign the string variable so that it becomes: select column1 from table2 where column2 = "text"
...by doing things like:

stringVariable = "select column1 from table2 where column2 = " & original variable name

...Then finally, execute the stringvariable:
CurrentDb.Execute stringVariable

Which the database will translate to: select column1 from table2 where column2 = "text"

I know you've gotten a lot of input, but when I was learning this, it took about 10 different explanations before about the 8th one clicked with me.
So just throwing it in the mix

 

[theDBguy]

Is the code repository forum on this site safe to download code and use it in my application?

I would like to think so. I think every submission to that forum has to be approved before it's visible to the public. I would hope harmful code would not get approved.

 

[slharman1]

no idea if this will help at all.

think of a sql statement, in its literal sense, like:

select column1 from table1 where column2 = "text"

Your job in VBA is to generate that literal string value

So you cannot go:

declare variable
assign variable a value
try to execute: select column1 from table2 where column2 = variablename

because, "variablename" isn't what the LITERAL sql statement would be, right?

you have to do:

declare variable (original)
also declare a string variable (we're adding this now)
assign original variable a value
now assign the string variable so that it becomes: select column1 from table2 where column2 = "text"
...by doing things like:


...Then finally, execute the stringvariable:
CurrentDb.Execute stringVariable

Which the database will translate to: select column1 from table2 where column2 = "text"

I know you've gotten a lot of input, but when I was learning this, it took about 10 different explanations before about the 8th one clicked with me.
So just throwing it in the mix

Issac, Yes that helps a lot!
Great explanation for me. When you don’t understand how this all works together it is hard to figure it out. And then when I finally do, I’ve got so much stuff rumbling around in my head I can’t really comprehend why.
thank you

 

[slharman1]

You do it by rewriting the SQL to take into account that EmpID2 is in the user interface, not in the SQL area managed by (separate process) ACE.

    SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = " &  CSTR( EmpID2 ) & " ; "

See my post #12 in this thread for an explanation

Solved - getting a second recordset based on a record in the first recordset

I can't seem to get the second SQL statement to work, access says "too few parameters, expected 1." set rs2 = is the line I am not getting right. all variables are set in the code above where I copied and pasted, rs and rs2 are declared as dao.recordsets and sql and sqltime are declared as...

www.access-programmers.co.uk

How do I determine what is in the user interface and what is the area managed by ACE? If there a paper I could read or is it simpler than that?

 

[arnelgp]

CStr() is redundant to your SQL2 expression.
the result is string anyway even without casting EmpID2 as string.

 

[Minty]

How do i run the code, by keeping the vba window open and click on the appropriate button on my form (or whatever else I do that runs code?)

Yes - spot on - normally this is the only way to make it work if you are picking up values from controls on a form.

 

[slharman1]

CStr() is redundant to your SQL2 expression.
the result is string anyway even without casting EmpID2 as string.

So, like this?

CStr() is redundant to your SQL2 expression.
the result is string anyway even without casting EmpID2 as string.

So like this?

    Dim empID2 As Long
    empID2 = Me.Parent.txtEmpID
    
    SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID =" & "EmpID2"

 

[arnelgp]

no, like this:

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID =" & EmpID2

 

[gemma-the-husky]

SYNTAX

@slharman1

I hope this helps.

take this, your first expression
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = EmpID2"
This is incorrect, as it will look for an EmpID of a number EmpID2, but the text EmpID2 isn't a number.

Even if Empid2 was a string, it isn't right either, as it needs to be
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = "EmpID2""
but this isn't correct, as you now have strangle matched " pairs.

you might be able to use this, as you have used single quotes and double quotes to disambiguate the resulr
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = 'EmpID2' "

But where [EmpID2] is actually a variable you have to do it differently again.

So you have to consider how this will look to SQL when the string is built with the actual value replacing the variable name, and this depends on the data type of the variable. If you don't get it right, you will get the wrong answer, or a syntax error. So you need the correct way to use EmpID2, taking into account the datatype of EmpID2

For a number it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = 12"
so you need
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = " & EmpID2 - no additional formatting
note that you don't need any formatting around the variable. Access will pass the string SQL2 to SQL correctly.


For a string it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpName = "Jim Smith""

now the problem is that Jim Smith is inside inverted commas, and the whole string is also inside inverted commas, so access/sql struggles to parse this and it won't work. This is what I showed at the top.

I tend to use chr(34) the ascii code for an inverted comma, and then you don't have to worry about getting double inverted commas, and using a combination of single and double quotation marks

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpName = " & chr(34) & EmpName & chr(34) - string placed inside " chars.
Access now passes the string to SQL correctly.
Alternatively, you can try to put a " inside two ' ' chars, or use two """" to obtain a single ", I struggle with both of these, but I find chr(34) easy.


For a date it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE StartDate = #10/09/2021#

Note that now the problem is that SQL will try to treat the date as an American date if it can, so instead of getting 10th Sept, you get October 9th. You have to use a format construct that forces access/SQL to use the correct date, which I haven't included here.

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE StartDate = " & "#" & StartDate & "#" - date placed inside # chars

 

[slharman1]

no, like this:

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID =" & EmpID2

Believe or not that what I thought was right after learning a bit. DocMan had me confused with the CStr() comment. It looks like he was turning my variable into a string. But when I get to my code I think his suggestion is what I used to make it work. So this has me confused. ACE can understand the variable?

no, like this:

So more like this, with one exception, the last line of code here has to have the Set removed from the line, correct?
So We Set a var to the CurrentDb, but we just write rst = db.OpenRecordset(SQL2) to open the RS just like we did with the var SQL2 ="Select string"

Can you help me undestand why it is done this way?
What is the reasoning behind using Set
Is it because in Access CurrentDb is an object and Recordset is not? if that's it, then what is a Recordset?
Thanks again!

Private Sub Form_Current()
    Dim db As DAO.Database
    Dim rst2 As Recordset
    Dim SQL2 As String
    Dim empID2 As Long
    empID2 = Me.Parent.txtEmpID
   
    SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID=" & empID2
    Set db = CurrentDb
    Set rst2 = db.OpenRecordset(SQL2)

 

[slharman1]

SYNTAX

@slharman1

I hope this helps.

take this, your first expression
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = EmpID2"
This is incorrect, as it will look for an EmpID of a number EmpID2, but the text EmpID2 isn't a number.

Even if Empid2 was a string, it isn't right either, as it needs to be
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = "EmpID2""
but this isn't correct, as you now have strangle matched " pairs.

you might be able to use this, as you have used single quotes and double quotes to disambiguate the resulr
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = 'EmpID2' "

But where [EmpID2] is actually a variable you have to do it differently again.

So you have to consider how this will look to SQL when the string is built with the actual value replacing the variable name, and this depends on the data type of the variable. If you don't get it right, you will get the wrong answer, or a syntax error. So you need the correct way to use EmpID2, taking into account the datatype of EmpID2

For a number it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = 12"
so you need
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpID = " & EmpID2 - no additional formatting
note that you don't need any formatting around the variable. Access will pass the string SQL2 to SQL correctly.


For a string it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpName = "Jim Smith""

now the problem is that Jim Smith is inside inverted commas, and the whole string is also inside inverted commas, so access/sql struggles to parse this and it won't work. This is what I showed at the top.

I tend to use chr(34) the ascii code for an inverted comma, and then you don't have to worry about getting double inverted commas, and using a combination of single and double quotation marks

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE EmpName = " & chr(34) & EmpName & chr(34) - string placed inside " chars.
Access now passes the string to SQL correctly.
Alternatively, you can try to put a " inside two ' ' chars, or use two """" to obtain a single ", I struggle with both of these, but I find chr(34) easy.


For a date it needs to look like this
SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE StartDate = #10/09/2021#

Note that now the problem is that SQL will try to treat the date as an American date if it can, so instead of getting 10th Sept, you get October 9th. You have to use a format construct that forces access/SQL to use the correct date, which I haven't included here.

SQL2 = "SELECT [WOitems] FROM tblTimeClock WHERE StartDate = " & "#" & StartDate & "#" - date placed inside # chars

Thanks!

 

[arnelgp]

Set is used when you are assigning an Object variable.
while Let (or simply without it/default) assigns scalar datatype
to a variable. simple type like string, number, etc.

 

[slharman1]

Set is used when you are assigning an Object variable.
while Let (or simply without it/default) assigns scalar datatype
to a variable. simple type like string, number, etc.

Thanks, I am starting to understand "why" we do things a certain way

 

[slharman1]

Set is used when you are assigning an Object variable.
while Let (or simply without it/default) assigns scalar datatype
to a variable. simple type like string, number, etc.

One more thing is it good practice to use Let when assigning a scaler type variable- do you use it?

 

[arnelgp]

Let is an old vb/vba statement.
you may or may not use it.
it is up to you.
I think it would be best to Use it so
your code will be consistent.

you have Set in your code as well
as Let.

 

[Gasman]

I would say the opposite, else you will get your set and let mixed up.
I have never used Let, I have to use Set.

 

[arnelgp]

I would say the opposite, else you will get your set and let mixed up.

there is no confusion, you will get error when you Let a variable to an Object.
same when you Set a variable to a string or number.