Solved refer to a number field in a sql statement

[slharman1]

access VB says
"Too few parameters"
How do I write it?

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

 

[Gasman]

Doing it again

 

[theDBguy]

Have I shown you this yet?

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

 

[The_Doc_Man]

access VB says
"Too few parameters"
How do I write it?

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

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

 

[slharman1]

Doing it again

I guess I am unsure about how to refer to controls or fields from different areas and of course, apparently I don’t get the concatenation syntax at all. But help please!

 

[The_Doc_Man]

Re-read the article I referenced. It will tell you why you need to use concatenation at all. Or did you not understand my explanation?

Think of Access as having TWO parts. The part that interacts with YOU and the part that interacts with your DATA. Your part is the User Interface (UI) and the data part is SQL. They are in TWO SEPARATE computer tasks. If you know Windows nomenclature, two PROCESSES. They each have their own memory. Therefore, the REASON for needing to concatenate stuff into a string is when you are doing something in the UI that has to interact with SQL, you need to prepare the SQL because SQL cannot see the UI except in certain very limited ways - and seeing variables isn't one of the exceptions.

 

[Gasman]

I guess I am unsure about how to refer to controls or fields from different areas and of course, apparently I don’t get the concatenation syntax at all. But help please!

You refer to them in the same way.

Strings need to be surrounded with ' unless it has a ' in it, then I *think* """ would work instead.
Dates need # surrounding them and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything.

Again for the umpteenth time, not just to you but to other members trying to concatenate, put the criteria into a string variable and Debug.Print that.
That should show you the errors, then just alter until you get it correct.

 

[Grumm]

You refer to them in the same way.

Strings need to be surrounded with ' unless it has a ' in it, then I *think* """ would work instead.
Dates need # surrounding them and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything.

Again for the umpteenth time, not just to you but to other members trying to concatenat, put the criteria into a string variable and Debug.Print that.
That should show you the errors, then just alter until you get it correct.

Isn't that what a dev do daily ? Just debug stuff and change it until it is fixed ?

 

[slharman1]

Re-read the article I referenced. It will tell you why you need to use concatenation at all. Or did you not understand my explanation?

Think of Access as having TWO parts. The part that interacts with YOU and the part that interacts with your DATA. Your part is the User Interface (UI) and the data part is SQL. They are in TWO SEPARATE computer tasks. If you know Windows nomenclature, two PROCESSES. They each have their own memory. Therefore, the REASON for needing to concatenate stuff into a string is when you are doing something in the UI that has to interact with SQL, you need to prepare the SQL because SQL cannot see the UI except in certain very limited ways - and seeing variables isn't one of the exceptions.

Thank you DonMan, I am very much a beginner but I have managed to accomplish some pretty good things. I really didn’t understand the explanation but I am learning. I really appreciate all the help you guys give me and I am definitely learning, but there is a lot to soak up.
again, thank you!

 

[slharman1]

Isn't that what a dev do daily ? Just debug stuff and change it until it is fixed ?

Unfortunately that’s what I have to do, except for the debug.print. Not even sure how to do that. That sounds like it would help a lot.

 

[MajP]

To help with proper delimiters and concatenation

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Minty]

Unfortunately that’s what I have to do, except for the debug.print. Not even sure how to do that. That sounds like it would help a lot.

In your code add

Debug.Print "In My Code x = " & MyVariable_X

At an appropriate point after you think you have set the variable to something.
Then in the immediate window in the VBA editor (press Ctrl + G to bring it up), when your code runs you will see the result.

 

[slharman1]

In your code add

Debug.Print "In My Code x = " & MyVariable_X

At an appropriate point after you think you have set the variable to something.
Then in the immediate window in the VBA editor (press Ctrl + G to bring it up), when your code runs you will see the result.

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?)

 

[slharman1]

To help with proper delimiters and concatenation

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

Heading to the link now to read. Thanks

 

[theDBguy]

Heading to the link now to read. Thanks

Just curious if you saw the link I posted earlier... I'd like to know if it was helpful or not (so I can update it). Cheers!

 

[slharman1]

Just curious if you saw the link I posted earlier... I'd like to know if it was helpful or not (so I can update it). Cheers!

I am going there after I finish reading MajP’s post.

 

[theDBguy]

I am going there after I finish reading MajP’s post.

Okay, thanks. I'm really interested in what you think of it.

 

[slharman1]

Heading to the link now to read. Thanks

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?

 

[slharman1]

Have I shown you this yet?

How to build strings in VBA

I promised a member at UtterAcess that I will write a blog about how to build a string in VBA, particularly when constructing a SQL statemen...

thedbguy.blogspot.com

Wow! I can now understand a "little" better, most of everything I read here is over my head and I just keep working until I accomplish the task, no matter how small. But your blog gives me insight as to why I am doing what needs to be done, and thus educating me. I have written a pretty extensive database app for work, and I am sure if I had more knowledge I could understand more and move at a faster, more error free pace.
Thank you much and let me know if you have any other lessons for beginner programmer's like myself.

 

[theDBguy]

Wow! I can now understand a "little" better, most of everything I read here is over my head and I just keep working until I accomplish the task, no matter how small. But your blog gives me insight as to why I am doing what needs to be done, and thus educating me. I have written a pretty extensive database app for work, and I am sure if I had more knowledge I could understand more and move at a faster, more error free pace.
Thank you much and let me know if you have any other lessons for beginner programmer's like myself.

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!