Help with a Query or SQL select statement..please

[ZaphodBBB]

Windows XP Pro , Access2003.

Hi I have a form called frmCheckSheet and a table called tblCheckSheet.

On the form there is a combobox cboSerialID which is multicolumn and when a serial number is selected the afterupdate event loads various other text boxes with customer name, department, type of equipment etc. One of the Text boxes on the form is txtAssessment, and it holds an assessment of the item checked...it is linked to a memo field in the tblCheckSheet.

When the user goes to create a new record I would like to have the previous assessment loaded into a textbox on the form so he/she can compare the condition of the equipment this time with how it was assessed the previous check. This Textbox would be loaded on the AfterUpdate event of the cboSerialID (so that it picks up the required instrument).
The query would also need to refer to the AutoDate (txtDate) Textbox on the new record (of the form)so that it picked up the previous check to whatever today's check date happened to be.

So I would like something like:

strSQL = "SELECT tblCheckSheet.Assessment From tblChecksheet WHERE tblCheckSheet.SerialID = cboSerialID.Column(0)" & -
"AND tblCheckSheet.Date < txtDate"

Does anyone have any idea of the exact syntax please? Would I have to make up a function that is included in the statement?

Hope the above is understandable and again thanks for any assistance.

[MMcCarthy]

Try

strSQL = "SELECT tblCheckSheet.Assessment From tblChecksheet " _
"WHERE tblCheckSheet.SerialID = " & Me.cboSerialID & _
" AND tblCheckSheet.Date<" & Format(Me.txtDate, '\#m/d/yyyy\#')
 

Mary

[ZaphodBBB]

HI Mary

Thanks again for your assistance. I tried it as you showed but I keep on getting an error with the Format part of your statement.

I tried it without the format and it still errors but for a different reason. If I do not add the Format statement, then when I get the error coming up and I go to debug mode and hover with the mouse over the part of your statement that shows cboSerialID, I get like a tooltip appear with say "123456" in it indicating the correct serial number and if I hover over txtDate I then see the correct (i.e. todays) date as say 23/12/2006.

I am using the statement as part of a querydef.

I tried changing around the ' and ' and the \ \ at the beginning and end of the Format but no go.

Not sure where you are but Merry Christmas from New Zealand and hope you have a great new year!

[MMcCarthy]

HI Mary

Thanks again for your assistance. I tried it as you showed but I keep on getting an error with the Format part of your statement.

I tried it without the format and it still errors but for a different reason. If I do not add the Format statement, then when I get the error coming up and I go to debug mode and hover with the mouse over the part of your statement that shows cboSerialID, I get like a tooltip appear with say "123456" in it indicating the correct serial number and if I hover over txtDate I then see the correct (i.e. todays) date as say 23/12/2006.

I am using the statement as part of a querydef.

I tried changing around the ' and ' and the \ \ at the beginning and end of the Format but no go.

Not sure where you are but Merry Christmas from New Zealand and hope you have a great new year!

Merry Christmas Zaphod

I'll have a look at this Tuesday/Wednesday.

Mary

[MMcCarthy]

Merry Christmas Zaphod

I'll have a look at this Tuesday/Wednesday.

Mary

Sorry Zaphod

Can you post a reply on Wednesday to remind me. ;)

Mary

[Killer42]

Thanks again for your assistance. I tried it as you showed but I keep on getting an error with the Format part of your statement.

Perhaps you are trying to apply a date format to a text field. You might try just concatenating "#", the date field (no Format function) and another "#", and see what happens.

Mary, this is another one you wanted to be reminded about.

[NeoPa]

Perhaps you are trying to apply a date format to a text field. You might try just concatenating "#", the date field (no Format function) and another "#", and see what happens.

Assuming the Me.txtDate field is in a recognisable date format (can be treated as a date) then the formatting Mary gave should be perfect.
Zaphod,
What sort of data are you falling over here?
Is it a compliation or processing error?
Can you post what and where the problem is.
(Let us know if you need help compiling it explicitly).

[Killer42]

NeoPa, do you think the error on the Format might indicate that we're trying to treat it as both a date and a string at the same time? What I mean is, the \# would seem to indicate formatting for a string, or maybe a number, while the m/d/yyyy obviously applies to a date. Perhaps VBA doesn't like to see them combined?

[NeoPa]

No Killer - the # character is the date separator character in SQL.
See this link for fuller details (Literal DateTimes and Their Delimiters (#).).

[Killer42]

No Killer - the # character is the date separator character in SQL.

I'm aware of that. But unless I'm mistaken, the "\" symbol just means to include the following character literally in the string. Thought it might not work when formatting a date.

See this link for fuller details (Literal DateTimes and Their Delimiters (#).).

Alright, I'll go and do my homework... grumble... mutter...

[Killer42]

...the "\" symbol just means to include the following character literally...

Damnit! For those very few who might care, I did of course mean the subsequent character, not "following".

[NeoPa]

Damnit! For those very few who might care, I did of course mean the subsequent character, not "following".

Obviously well educated Killer :).
If you have any problems with the formatting of dates etc (They can really be a bitch - especially when trying to figure out which interpreter is interpreting which part of your original string) let me know and I'll try to help.

[Killer42]

Obviously well educated Killer :).
If you have any problems with the formatting of dates etc (They can really be a bitch - especially when trying to figure out which interpreter is interpreting which part of your original string) let me know and I'll try to help.

Hm... not too sure about that. You never did find me the right format string to format my greater-than-24-hour time periods. :)

Oh boy, this thread has rather run off the rails, huh.

[NeoPa]

I didn't find an in-built format (There wasn't one) but I got you a usable work-around as I recall ;).
Harsh and churlish criticism :(.
...& yes - it has.

[ZaphodBBB]

Hi

Just back from holiday so first chance to have another look back here. Hope any in this part of the world had a great Xmas and New Year!

This is exactly how I have typed what Mary provided:

(Line 1) strSQL = "SELECT tblCheckSheet.Assessment FROM tblCheckSheet " & _
(Line 2) " WHERE tblCheckSheet.SerialID =Me.cboSerialID.Column(0) & _
(Line 3)"AND tblCheckSheet.JobDate < " & Format(Me.txtDate, '\#d/m/yyyy#\')

I keep getting "Compile Error expected:expression"
and the cursor is on the ' before the \#d/m/yyyy part.

In the table the date field is called JobDate its datatype is of Date/Time and it is formatted as Short Date.

The Default Value (for the txtDate textbox) on the form is =Date()

Any ideas much appreciated!!
I have tried changing the formatting with no joy and did try removing all the formatting but then get another error.. think it was a data mismatch but will check again if useful.

Thanks again

[NeoPa]

Try :

strSQL = "SELECT tblCheckSheet.Assessment FROM tblCheckSheet " & _ 
"WHERE tblCheckSheet.SerialID =" & Me.cboSerialID.Column(0) & _
" AND tblCheckSheet.JobDate < " & Format(Me.txtDate, '\#d/m/yyyy\#')

You had a couple of small errors in it.

[Killer42]

Try :

strSQL = "SELECT tblCheckSheet.Assessment FROM tblCheckSheet " & _ 
"WHERE tblCheckSheet.SerialID =" & Me.cboSerialID.Column(0) & _
" AND tblCheckSheet.JobDate < " & Format(Me.txtDate, '\#d/m/yyyy\#')

You had a couple of small errors in it.

Doesn't Format( ) need double quotes?

[MMcCarthy]

Doesn't Format( ) need double quotes?

When inside a vba statement like this the double quotes would confuse the engine so single quotes are uses. Perfectly acceptable.

Mary

[Killer42]

When inside a vba statement like this the double quotes would confuse the engine so single quotes are uses. Perfectly acceptable.

Are you sure? (Guess I should go try it and see for myself, huh.)

However, I thought it was inside SQL strings that the single quotes were acceptable. This Format function was used in perfectly normal code, in the process of building a string.

[MMcCarthy]

Are you sure? (Guess I should go try it and see for myself, huh.)

However, I thought it was inside SQL strings that the single quotes were acceptable. This Format function was used in perfectly normal code, in the process of building a string.

You're right it was. Still the single quotes should be fine although in this case double quotes could have been used.

Mary

[Killer42]

You're right it was. Still the single quotes should be fine although in this case double quotes could have been used.

Hm... while I acknowledge both you and NeoPa as much more knowledgeable in Access, I feel I may have proved you wrong on this one. I just pasted NeoPa's code into an event procedure behind an Access (2000) form and it wouldn't pass the syntax check until I changed the single quotes to double.

Any chance this is version-specific?

[MMcCarthy]

Hm... while I acknowledge both you and NeoPa as much more knowledgeable in Access, I feel I may have proved you wrong on this one. I just pasted NeoPa's code into an event procedure behind an Access (2000) form and it wouldn't pass the syntax check until I changed the single quotes to double.

Any chance this is version-specific?

No and I can't say I'm terribly surprised.

Unfortunately, when you code as much as I do you don't question enough why you do things the way you do and in this case I would have used double quotes. It makes sense when I think about it. The comment indicator in vba is a single quote so therefore the single quotes couldn't have worked outside the statement.

Points to you on this one.

Mary

[Killer42]

Thanks Mary. So, for anyone coming in late, this would mean the code needs to be modified as follows...

strSQL = "SELECT tblCheckSheet.Assessment FROM tblCheckSheet " & _
"WHERE tblCheckSheet.SerialID = " & Me.cboSerialID.Column(0) & _
" AND tblCheckSheet.JobDate < " & Format(Me.txtDate, "\#d/m/yyyy\#")

And by the way, I'd like to pass along a little convention I use which often saves debugging time. In building a string over multiple lines like this, I believe it helps if you try to be consistent with where you put the extra space.

What I mean is, when you have a word break at the end of a line (such as between tblCheckSheet and WHERE above) you should put the space either (a) always on the end of the first line, or (b) always at the start of the second line. It makes no difference to the computer, of course, but can help the developer, and makes it less likely that you'll miss it (serious) or insert an extra space (usually trivial).

P.S. The AND was a special case here, of course.

[NeoPa]

Doesn't Format( ) need double quotes?

NO - absolutely not!
Within SQL code it should always have single quote (').
In VB & VBA Double-Quotes are used (").

Forget all that - I replied quickly and didn't get the following posts.
You are absolutely right!
This was in VBA and should have used Double-Quotes (") - My bad.

[NeoPa]

Thanks Mary. So, for anyone coming in late, this would mean the code needs to be modified as follows...

strSQL = "SELECT tblCheckSheet.Assessment FROM tblCheckSheet " & _
"WHERE tblCheckSheet.SerialID = " & Me.cboSerialID.Column(0) & _
" AND tblCheckSheet.JobDate < " & Format(Me.txtDate, "\#d/m/yyyy\#")

And by the way, I'd like to pass along a little convention I use which often saves debugging time. In building a string over multiple lines like this, I believe it helps if you try to be consistent with where you put the extra space.

What I mean is, when you have a word break at the end of a line (such as between tblCheckSheet and WHERE above) you should put the space either (a) always on the end of the first line, or (b) always at the start of the second line. It makes no difference to the computer, of course, but can help the developer, and makes it less likely that you'll miss it (serious) or insert an extra space (usually trivial).

P.S. The AND was a special case here, of course.

Now I've caught up with all the posts : phew :
I did doctor the original SQL with this in mind and you hit on the reason that I (unusually) left the space at the start - which was because the end of the previous line was a variable and adding a space would have been more complicated - both for writing as well as reading.
I absolutely agree with your point on consistency here though - if that was not already clear :).
While posting, I'd like to confirm what Mary said - You caught me out in a mistake with the quote thing and I'm grateful you brought it into the open so that my code didn't fail for anyone trying it.

[Killer42]

While posting, I'd like to confirm what Mary said - You caught me out in a mistake with the quote thing and I'm grateful you brought it into the open so that my code didn't fail for anyone trying it.

Cool! Any time you need someone to pick holes in your code, just ask. :D

[NeoPa]

Cool! Any time you need someone to pick holes in your code, just ask. :D

I've never needed to ask before! :D

[Killer42]

I've never needed to ask before! :D

True.

You could try asking me not to, but I don't promise anything. :)

[ZaphodBBB]

O.K. thanks for the code...that worked fine Killer42! Thanks.
I dont know about all the usages for ' and " within or without SQL or VBA or whatever at the moment. I have read a few things about it but so far nothing definitive that doesn't warp my brain.

So again all thanks very much for all your assistance!

[NeoPa]

I hope this helps (Quotes (') and Double-Quotes (") - Where and When to use them.).

[ZaphodBBB]

Thanks NeoPa

What do you mean by
....From time to time, you will come across the requirement to specify a string which contains the character which you are using to delimit your string (' or ")....

I dont see what the point is of putting "Bob" inside a string with quotes around it?

strDisp = "Please select ""Bob"" from your list." what use is that?

Just taking a little time to try and understand the ' and " post. Will have bulk more questions I think.

Thanks

[ZaphodBBB]

Why do you need the Carriage Returns and Line Feeds for?
Most of the multiline SQL statements the people on this site have provided for me do not include them? Is that just technically correct way of doing them..but little used?

strSQL = "SELECT *" & VbCrLf & _
"FROM [TableName]" & VbCrLf & _
"WHERE ([AccountName]='" & Me.cboAccount & "')"


Thanks again

[ZaphodBBB]

Hi again NeoPa

In the Using ' and " tutorial or post there is near the bottom the following.

strSQL = "SELECT *" & VbCrLf & _
"FROM [TableName]" & VbCrLf & _
"WHERE ([AccountName]='" & Me.cboAccount & "')"

Why do you need the & (ampersand character) following the Me.cboAccounts?

Is the way you have outlined the statement above the simplist way to create such a statement? If not could you please show me the simplist way you could possibly write the above statement that would function o.k. as I am finding it a bit difficult to visulize the purpose of the ' followed by the ".
I think I understand that you are in effect telling the SQL interpreter to look in cboAccount, take whatever is in there and add it to the string as if it were a string literal... but are a single accompanied by a double" required for the interpreter to know how to treat what lies inbetween the two sets of ' "....." ' ?

Hopefully you will have the patience to answer these questions!

Thanks again.

[MMcCarthy]

Thanks NeoPa

What do you mean by
....From time to time, you will come across the requirement to specify a string which contains the character which you are using to delimit your string (' or ")....

I dont see what the point is of putting "Bob" inside a string with quotes around it?

strDisp = "Please select ""Bob"" from your list." what use is that?

Just taking a little time to try and understand the ' and " post. Will have bulk more questions I think.

Thanks

If you have a query statement you are trying to make then you will need the single quotes as follows:

Dim strSQL As String
 
   strSQL = "SELECT CompanyID FROM tblInvoices " & _
                   "WHERE CompanyName='thescripts';"
 

As you can see because the criteria is passing a string value it needs to be surrounded by quotes. If double quotes were used the string would be broken so single quotes are used instead.

Mary

[Killer42]

Why do you need the Carriage Returns and Line Feeds for?

I think you'll find that's just for readability. The SQL string could be (and often is) one huge, long line. But then if you print it or copy/paste it somewhere, it might be difficult to read. The line breaks make things more convenient but as you noticed, are not required.

[Killer42]

... In the Using ' and " tutorial or post there is near the bottom the following.

strSQL = "SELECT *" & VbCrLf & _
         "FROM [TableName]" & VbCrLf & _
         "WHERE ([AccountName]='" & Me.cboAccount & "')"
 

Why do you need the & (ampersand character) following the Me.cboAccounts?

The last ampersand is being used to add the ') characters onto the end of the string. For example, if Me.cboAccount had the value "ABC" this would produce...

SELECT *
FROM [TableName]
WHERE ([AccountName]='ABC')"

I've underlined the part which was added by that last ampersand.

Is the way you have outlined the statement above the simplist way to create such a statement? If not could you please show me the simplist way you could possibly write the above statement that would function o.k. as I am finding it a bit difficult to visulize the purpose of the ' followed by the ".
I think I understand that you are in effect telling the SQL interpreter to look in cboAccount, take whatever is in there and add it to the string as if it were a string literal... but are a single accompanied by a double" required for the interpreter to know how to treat what lies inbetween the two sets of ' "....." ' ?

I think the problem is that you are confusing the SQL statement, and the VB code which is being used to put it together inside a string variable. We could simplify the statement a bit to help...

strSQL = "SELECT * FROM [TableName] WHERE ([AccountName] = 'ABC')"
  Or, to illustrate with a Number rather than a Text field...
strSQL = "SELECT * FROM [TableName] WHERE ([AccountNum] = 123)"

Compare this to the actual SQL statement which is now held in the string...

SELECT * FROM [TableName] WHERE ([AccountName] = 'ABC')

The double quotes (") are being used in VB to tell the computer this is a string of characters to use as they are - don't try to interpret them as a variable name or statement or whatever.

The SQL statement also includes a string (ABC) to be searched for, and the SQL interpreter requires (single) quotes around this.

It's important to remember that there are two separate steps being performed.

• First we are telling VB to concatenate these various bits and pieces of data (including the value of a control called cboAccount, probably a combobox) into a string variable. In this case the variable happens to be called strSQL, but that is not significant. Remeber, at this point we have not done anything with the database, just created a string of text.
• At some later point we will, presumably, pass this string to the SQL interpreter, which will try to process the SQL statement it contains.

Hopefully you will have the patience to answer these questions!

I wouldn't worry about it too much. There are lots of people here who are happy to help out. You will probably see at least one post pointing you to a tutorial or something - there's more than one place to learn this stuff. :)

In fact, just because I'm in a voluble mood, here's a final example which strips the statement down to just about as simple as you're likely to see...

MyStringVar = "SELECT * FROM TableName WHERE AccountNum = 123"

Or, to include our control value again...

MyStringVar = "SELECT * FROM TableName WHERE AccountNum = " & cboAccount

[NeoPa]

Thanks NeoPa

What do you mean by
....From time to time, you will come across the requirement to specify a string which contains the character which you are using to delimit your string (' or ")....

I dont see what the point is of putting "Bob" inside a string with quotes around it?

strDisp = "Please select ""Bob"" from your list." what use is that?

Just taking a little time to try and understand the ' and " post. Will have bulk more questions I think.

Thanks

Zaphod,
"Please select ""Bob"" from your list." would create a string :
Please select "Bob" from your list.
This is actually standard English usage - as you are identifying "Bob" as a literal item rather than using it's normal meaning. This is often ignored but that is incorrect usage of the language. It is often used with names of files.
EG.
Please open the spreadsheet "AccountCodes.Xls" before following the instructions therein.
Really though, it is just an example of where you might want to include the same character within a string - as that used to delimit the string.

-Adrian.

[NeoPa]

Why do you need the Carriage Returns and Line Feeds for?
Most of the multiline SQL statements the people on this site have provided for me do not include them? Is that just technically correct way of doing them..but little used?

strSQL = "SELECT *" & VbCrLf & _
"FROM [TableName]" & VbCrLf & _
"WHERE ([AccountName]='" & Me.cboAccount & "')"


Thanks again

No, they are both perfectly valid ways of using SQL strings.
I use the line break characters because I will often print the string in Debug mode to check that it says what I expect.
I find it's output is neater and easier to read if displayed on multiple lines. Just a simple answer this time ;).

[NeoPa]

Hi again NeoPa

In the Using ' and " tutorial or post there is near the bottom the following.

strSQL = "SELECT *" & VbCrLf & _
"FROM [TableName]" & VbCrLf & _
"WHERE ([AccountName]='" & Me.cboAccount & "')"

Why do you need the & (ampersand character) following the Me.cboAccounts?

Is the way you have outlined the statement above the simplist way to create such a statement? If not could you please show me the simplist way you could possibly write the above statement that would function o.k. as I am finding it a bit difficult to visulize the purpose of the ' followed by the ".
I think I understand that you are in effect telling the SQL interpreter to look in cboAccount, take whatever is in there and add it to the string as if it were a string literal... but are a single accompanied by a double" required for the interpreter to know how to treat what lies inbetween the two sets of ' "....." ' ?

Hopefully you will have the patience to answer these questions!

Thanks again.

In short - Yes it is the simplest way.
Other than the layout (using any number of 'White-space' characters together is equivalent to using any one of them individually as far as SQL is concerned.) the SQL is the shortest I can make it.
The quotes, in the order found there, are critical and are, in fact, the crux of the whole matter.
To understand what is happening you need to think of the string (strSQL) after it's been created in VBA and before it gets passed to SQL. Bear in mind that the SQL interpreter has no idea (nor does it care) how the string was created and also that the SQL interpreter would have no idea how to understand 'Me.cboAccount'. The VBA is responsible for building a string which has the value of Me.cboAccount embedded within it. Of course, for SQL to understand it as a value and not the name of some field, it must be delimited (surrounded) by single-quotes ('). The '&' (after the Me.cboAccounts) is necessary here to finish building the SQL string in VBA.
I hope you can now understand why each piece of the puzzle is necessary.

[NeoPa]

As you can see because the criteria is passing a string value it needs to be surrounded by quotes. If double quotes were used the string would be broken so single quotes are used instead.

In fact, although this reasoning is in common use, the reason that I would use the single-quote (') in this instance is because it is the SQL Standard character for string delineation.
Access is flexible about allowing double-quotes (") but Microsoft Query is not so forgiving (nor is a whole raft of other M$ & non-M$ software) so, if you want to avoid portability issues, or even human errors due to habitually using the wrong quote character, get into the habit of using the correct one, even in Access.

[NeoPa]

Having just read Killer's post, I can now see that the answers are well laid out there too. If my answers don't make sense to you then they may well do.

[Killer42]

Having just read Killer's post, I can now see that the answers are well laid out there too. If my answers don't make sense to you then they may well do.

:) Thank for the credit.

I was just reading through your posts, and kept thinking "That's what I said!".

I did get a bit carried away last night, I suppose.

[NeoPa]

That's very true Killer.
I have to deal with things one at a time otherwise things get too complicated, and I only read your response after I'd dealt with the previous questions. I have to say I was entirely happy with what I read.

[Killer42]

That's very true Killer.
I have to deal with things one at a time otherwise things get too complicated, and I only read your response after I'd dealt with the previous questions. I have to say I was entirely happy with what I read.

I'm glad of that. Think I must have spent half an hour or more composing and editing that message.

[ZaphodBBB]

Hi Killer

Thanks a lot for your reply here... though I dont profess to understand all the in's and out's of Access' syntax, I think I'm beginning to get a little more of it.

[Killer42]

Hi Killer

Thanks a lot for your reply here... though I dont profess to understand all the in's and out's of Access' syntax, I think I'm beginning to get a little more of it.

I'm in the same boat. (Possibly sitting a little closer to the front, that's all. :))