String inside SQL

G

GLT

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay ON
[qry_(1)ServersAndSubBackups].ServSubID = tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the current day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
 
D

Daniel Pineault

I am by no means an experts on SQL, but typically I always surround string
within single quotes. I also noticed a lost ] in your SQL string?

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay ON
[qry_(1)ServersAndSubBackups].ServSubID = tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = '" & strDay & "'))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.
 
K

Ken Sheridan

As Daniel has said you need to delimit the value with quotes characters, but
if the value is always the current day then you don't need the variable at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the strings
to denote a literal quotes character. The alternative is to use a single
quotes character as Daniel did, which will be fine in this context, but would
be a problem with strings containing apostrophes, e.g. Irish names like
O'Siridean (which just happens to be mine in its un-anglicized form, though
strictly its Ó Siridean). If values can contain both single and double
quotes characters then you have to resort to a more elaborate solution by
using the Replace function to substitute another character for each when
comparing values. A High ASCII character like the tilde is often used, e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at the
end of each line, so the expression will run things together e.g.

"…..tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]….."

would evaluate as

…..tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]…..

Sometimes the lack of a space won't matter, but often it will produce an
error.

Ken Sheridan
Stafford, England
 
G

GLT

Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then I was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as well.
These quotes in SQL drive me up the wall...

Ken Sheridan said:
As Daniel has said you need to delimit the value with quotes characters, but
if the value is always the current day then you don't need the variable at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the strings
to denote a literal quotes character. The alternative is to use a single
quotes character as Daniel did, which will be fine in this context, but would
be a problem with strings containing apostrophes, e.g. Irish names like
O'Siridean (which just happens to be mine in its un-anglicized form, though
strictly its Ó Siridean). If values can contain both single and double
quotes characters then you have to resort to a more elaborate solution by
using the Replace function to substitute another character for each when
comparing values. A High ASCII character like the tilde is often used, e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at the
end of each line, so the expression will run things together e.g.

"…..tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]….."

would evaluate as

…..tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]…..

Sometimes the lack of a space won't matter, but often it will produce an
error.

Ken Sheridan
Stafford, England

GLT said:
Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay ON
[qry_(1)ServersAndSubBackups].ServSubID = tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the current day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as well.
These quotes in SQL drive me up the wall...

Ken Sheridan said:
As Daniel has said you need to delimit the value with quotes characters,
but
if the value is always the current day then you don't need the variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a single
quotes character as Daniel did, which will be fine in this context, but
would
be a problem with strings containing apostrophes, e.g. Irish names like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and double
quotes characters then you have to resort to a more elaborate solution by
using the Replace function to substitute another character for each when
comparing values. A High ASCII character like the tilde is often used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce an
error.

Ken Sheridan
Stafford, England

GLT said:
Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
G

GLT

Hi Douglas,

Thanks for your reply - I tried this as follows:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = " & _
"Format(VBA.Date, ""ddd"") & "'" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Now everything turns green after the single quote - what is the purpose of
the single quote in the first place? If i remove the single quote I get a
run time error...

Thanks,
GLT.

Douglas J. Steele said:
In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as well.
These quotes in SQL drive me up the wall...

Ken Sheridan said:
As Daniel has said you need to delimit the value with quotes characters,
but
if the value is always the current day then you don't need the variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a single
quotes character as Daniel did, which will be fine in this context, but
would
be a problem with strings containing apostrophes, e.g. Irish names like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and double
quotes characters then you have to resort to a more elaborate solution by
using the Replace function to substitute another character for each when
comparing values. A High ASCII character like the tilde is often used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce an
error.

Ken Sheridan
Stafford, England

:

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The single quotes are required because tbl_Perm_ServNameSubDay.Day is
obviously a text field or you wouldn't be trying to pass it Mon, Tue, etc.
In actual fact, though, I mislead you: the Format statement shouldn't be in
quotes, so the double quotes aren't required in this case. You're also
missing the initial single quote. (The explanation of why everything is
turning green is that your single quote isn't inside double quotes, so
Access sees it as a comment delimiter)

As well, you should rename the field: Day is a reserved word, and you should
use reserved words for your own purpose. (For a comprehensive list of names
to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html) If you cannot (or will not)
rename the field, at least put square brackets around it:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.[Day] " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.[Day] = '" & _
Format(VBA.Date, "ddd") & "' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Exagerated for clarity, that's

"WHERE tbl_Perm_ServNameSubDay.[Day] = ' " & _
Format(VBA.Date, "ddd") & " ' " & _

Note that you need the space after the second single quote (or else one
before ORDER BY)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Douglas,

Thanks for your reply - I tried this as follows:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = " & _
"Format(VBA.Date, ""ddd"") & "'" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Now everything turns green after the single quote - what is the purpose of
the single quote in the first place? If i remove the single quote I get a
run time error...

Thanks,
GLT.

Douglas J. Steele said:
In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in
the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then
I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as
well.
These quotes in SQL drive me up the wall...

:

As Daniel has said you need to delimit the value with quotes
characters,
but
if the value is always the current day then you don't need the
variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a
single
quotes character as Daniel did, which will be fine in this context,
but
would
be a problem with strings containing apostrophes, e.g. Irish names
like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and
double
quotes characters then you have to resort to a more elaborate solution
by
using the Replace function to substitute another character for each
when
comparing values. A High ASCII character like the tilde is often
used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce
an
error.

Ken Sheridan
Stafford, England

:

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN
tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the
current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
G

GLT

Hi Douglas,

Thanks for your detailed response, I really appreciate it.

When I tried to run the SQL, i received the following error:

Run-Time Error '2342': The Run RunSQL
Action requires an argument consisting of an SQL
Statement

I read in another post:

DoCmd.RunSQL only works when data is being manipulated.
I mean an UPDATE, INSERT INTO, DELETE,GRANT... works fine using the
DoCmd.RunSQL command but a select statement only reads data so you cannot use
it.

Is there some other way I should run this SQL?

Cheers,
GLT.

Douglas J. Steele said:
The single quotes are required because tbl_Perm_ServNameSubDay.Day is
obviously a text field or you wouldn't be trying to pass it Mon, Tue, etc.
In actual fact, though, I mislead you: the Format statement shouldn't be in
quotes, so the double quotes aren't required in this case. You're also
missing the initial single quote. (The explanation of why everything is
turning green is that your single quote isn't inside double quotes, so
Access sees it as a comment delimiter)

As well, you should rename the field: Day is a reserved word, and you should
use reserved words for your own purpose. (For a comprehensive list of names
to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html) If you cannot (or will not)
rename the field, at least put square brackets around it:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.[Day] " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.[Day] = '" & _
Format(VBA.Date, "ddd") & "' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Exagerated for clarity, that's

"WHERE tbl_Perm_ServNameSubDay.[Day] = ' " & _
Format(VBA.Date, "ddd") & " ' " & _

Note that you need the space after the second single quote (or else one
before ORDER BY)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Douglas,

Thanks for your reply - I tried this as follows:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = " & _
"Format(VBA.Date, ""ddd"") & "'" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Now everything turns green after the single quote - what is the purpose of
the single quote in the first place? If i remove the single quote I get a
run time error...

Thanks,
GLT.

Douglas J. Steele said:
In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in
the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then
I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as
well.
These quotes in SQL drive me up the wall...

:

As Daniel has said you need to delimit the value with quotes
characters,
but
if the value is always the current day then you don't need the
variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a
single
quotes character as Daniel did, which will be fine in this context,
but
would
be a problem with strings containing apostrophes, e.g. Irish names
like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and
double
quotes characters then you have to resort to a more elaborate solution
by
using the Replace function to substitute another character for each
when
comparing values. A High ASCII character like the tilde is often
used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce
an
error.

Ken Sheridan
Stafford, England

:

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN
tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the
current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Better, I think, to concatenate the return value of the Format function into
the string expression:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date, "ddd") & """ " & _

You're right about the following quotes, though. I'd picked-and-mixed
double and single quotes!

Ken Sheridan
Stafford, England

Douglas J. Steele said:
In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as well.
These quotes in SQL drive me up the wall...

Ken Sheridan said:
As Daniel has said you need to delimit the value with quotes characters,
but
if the value is always the current day then you don't need the variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a single
quotes character as Daniel did, which will be fine in this context, but
would
be a problem with strings containing apostrophes, e.g. Irish names like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and double
quotes characters then you have to resort to a more elaborate solution by
using the Replace function to substitute another character for each when
comparing values. A High ASCII character like the tilde is often used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce an
error.

Ken Sheridan
Stafford, England

:

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName, tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

One way would be to assign the string to the SQL property of a temporary
querydef object. Paste the following procedure into a standard module.

Public Sub OpenTempQuery(strSQL As String)

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim strQdf As String
Static n As Integer

Set dbs = CurrentDb

n = n + 1
strQdf = "qdfTemp" & n

' create temporary QueryDef object
Set qdf = dbs.CreateQueryDef(strQdf, strSQL)

DoCmd.OpenQuery strQdf

' delete temporary QueryDef object
dbs.QueryDefs.Delete qdf.Name

End Sub

Then in your code call it with:

OpenTempQuery strSQL

If you call it more than once in a single session each temporary query will
be given a sequential numeric suffix, qdfTemp1, qdfTemp2 and so on. This is
achieved by using a Static variable n, so the value of n increments by 1 each
time the procedure is called, rather than starting from an initial value of
zero each time as would be the case if Dim were used instead of Static. The
effect of this is that each query's results can be left visible on the screen
while others are opened if desired.

Ken Sheridan
Stafford, England

GLT said:
Hi Douglas,

Thanks for your detailed response, I really appreciate it.

When I tried to run the SQL, i received the following error:

Run-Time Error '2342': The Run RunSQL
Action requires an argument consisting of an SQL
Statement

I read in another post:

DoCmd.RunSQL only works when data is being manipulated.
I mean an UPDATE, INSERT INTO, DELETE,GRANT... works fine using the
DoCmd.RunSQL command but a select statement only reads data so you cannot use
it.

Is there some other way I should run this SQL?

Cheers,
GLT.

Douglas J. Steele said:
The single quotes are required because tbl_Perm_ServNameSubDay.Day is
obviously a text field or you wouldn't be trying to pass it Mon, Tue, etc.
In actual fact, though, I mislead you: the Format statement shouldn't be in
quotes, so the double quotes aren't required in this case. You're also
missing the initial single quote. (The explanation of why everything is
turning green is that your single quote isn't inside double quotes, so
Access sees it as a comment delimiter)

As well, you should rename the field: Day is a reserved word, and you should
use reserved words for your own purpose. (For a comprehensive list of names
to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html) If you cannot (or will not)
rename the field, at least put square brackets around it:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.[Day] " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.[Day] = '" & _
Format(VBA.Date, "ddd") & "' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Exagerated for clarity, that's

"WHERE tbl_Perm_ServNameSubDay.[Day] = ' " & _
Format(VBA.Date, "ddd") & " ' " & _

Note that you need the space after the second single quote (or else one
before ORDER BY)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


GLT said:
Hi Douglas,

Thanks for your reply - I tried this as follows:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = " & _
"Format(VBA.Date, ""ddd"") & "'" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

Now everything turns green after the single quote - what is the purpose of
the single quote in the first place? If i remove the single quote I get a
run time error...

Thanks,
GLT.

:

In order to include quotes inside of quotes, you need to double them up:

"Format(VBA.Date, ""ddd"") & "'" & _

(note, too, the other correction I made to your quotes after the Format
statement)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Ken,

Thanks to both yourself and Daniel's responses,

I have edited my sql statement and it now looks like this:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name], " & _
"tbl_Perm_ServNameSubDay.Day " & _
"FROM [qry_(1)ServersAndSubBackups] " & _
"LEFT JOIN tbl_Perm_ServNameSubDay ON " & _
"[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID " & _
"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
"Format(VBA.Date, "ddd") & "" ' " & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client, " & _
"[qry_(1)ServersAndSubBackups].ServName, " & _
"tbl_Perm_ServNameSubDay.[Backup Name];"

It still says I have a syntax error - I do understand using the
Format(VBA.Date,"ddd") to extract the current day (its what i used in
the
strDay This works for Tue - Fri, but on Mondays (ie. strDay = mon) then
I
was
hoping to have strDay= "sun" or "sat" or "fri" in the above sql as
well.
These quotes in SQL drive me up the wall...

:

As Daniel has said you need to delimit the value with quotes
characters,
but
if the value is always the current day then you don't need the
variable
at
all. You can use:

"WHERE tbl_Perm_ServNameSubDay.Day = """ & _
Format(VBA.Date,"ddd") & ""' " & _

Note the use of the pair of contiguous quotes characters within the
strings
to denote a literal quotes character. The alternative is to use a
single
quotes character as Daniel did, which will be fine in this context,
but
would
be a problem with strings containing apostrophes, e.g. Irish names
like
O'Siridean (which just happens to be mine in its un-anglicized form,
though
strictly its Ó Siridean). If values can contain both single and
double
quotes characters then you have to resort to a more elaborate solution
by
using the Replace function to substitute another character for each
when
comparing values. A High ASCII character like the tilde is often
used,
e.g.

strName = "Cináed ""Born of Fire"" O'Siridean"
strFind = "O'Siridean"
strName = Replace(strName,"""","~")
strName = Replace(strName,"'","~")
strFind = Replace(strName,"""","~")
strFind = Replace(strName,"'","~")
? strName Like "*" & strFind & "*"
True

Another thing about your code is that you are not including a space at
the
end of each line, so the expression will run things together e.g.

"...tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups]..."

would evaluate as

...tbl_Perm_ServNameSubDay.DayFROM [qry_(1)ServersAndSubBackups]...

Sometimes the lack of a space won't matter, but often it will produce
an
error.

Ken Sheridan
Stafford, England

:

Hi,

would anyone be able to help with the following SQL:

strSQL = "SELECT [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name], tbl_Perm_ServNameSubDay.Day" & _
"FROM [qry_(1)ServersAndSubBackups] LEFT JOIN
tbl_Perm_ServNameSubDay
ON
[qry_(1)ServersAndSubBackups].ServSubID =
tbl_Perm_ServNameSubDay.ServSubID"
& _
"WHERE (((tbl_Perm_ServNameSubDay.Day) = " & strDay & "]))" & _
"ORDER BY [qry_(1)ServersAndSubBackups].Client,
[qry_(1)ServersAndSubBackups].ServName,
tbl_Perm_ServNameSubDay.[Backup
Name];"

I am trying to use the string called strDay (which contains the
current
day
ie. mon = monday, tue = tuesday etc).

I would also like to put a condition when strDay = "mon", the where
extracts
"sun" or "sat" or "sun"...

Is this possible or will it make the SQL to complicated?

Any assistance would be greatly appreciated.

Cheers,
GLT.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

return without gosub error 0
Sorting day names chronologically 5
Working with Calendar Days 1
Time Sheet End of Month 1
Subtotal with additional criteria 1
Adding days to the WORKDAY function 1
lookup on 2 (and 3!) dimensions 2
Code to name numerous ranges 11

Top