Is it possible to use quotes ("") in a string in VBA?

[Niklas Östrergren]

Hi!

Is it possible to use qoutes ("") in a string in VBA?

I´m trying to create a SQL-string to be used to open a recordset but I need
to format some fields in this SQL-string in which qoutes are used. But I get
an error (text = red) when I try this code (below):

strSQL = "SELECT qryValidMemberShip.*, Format([MemberShipEndDate],"yyyy") AS
ValidMemberShipYear" _
& " FROM qryValidMemberShip" _
& " WHERE qryValidMemberShip.fkMemberShipTypeID =" & 5 & " AND
Format(qryValidMemberShip.MemberShipEndDate,"yyyy") =" &
Format(Date(),"yyyy"))"


It highlights the first "yyyy"!

Any idéas of how I solve this?

TIA!
// Niklas

 

[Allen Browne]

The convention is to double the quotes if they are embeded in other quotes.

To get:
This string has a "word" in quotes
you use:
"This string has a ""word"" in quotes"

 

[Steve Huff]

Use 'yyyy' instead.

-Steve Huff
http://www.huffs.us

 

[Steve Huff]

Also, if the need arises you can use CHR(34) for a "" in a string by saying
somehting like:

somevar = "Select * from sometable where " & CHR(34) & someothervar &
CHR(34) & ";"

however in that example you could use the ' also...

-Steve Huff
http://www.huffs.us

 

[Niklas Östergren]

OK!

I´ll try this a little bit later today!

Thank´s
// Niklas

The convention is to double the quotes if they are embeded in other quotes.

To get:
This string has a "word" in quotes
you use:
"This string has a ""word"" in quotes"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi!

Is it possible to use qoutes ("") in a string in VBA?

I4m trying to create a SQL-string to be used to open a recordset but I
need
to format some fields in this SQL-string in which qoutes are used. But I
get
an error (text = red) when I try this code (below):

strSQL = "SELECT qryValidMemberShip.*, Format([MemberShipEndDate],"yyyy")
AS
ValidMemberShipYear" _
& " FROM qryValidMemberShip" _
& " WHERE qryValidMemberShip.fkMemberShipTypeID =" & 5 & " AND
Format(qryValidMemberShip.MemberShipEndDate,"yyyy") =" &
Format(Date(),"yyyy"))"


It highlights the first "yyyy"!

Any idias of how I solve this?

TIA!
// Niklas

 

[Niklas Östergren]

Hi Steve!

I´ll try your method as well! I have got two answeres with two different
way´s of doing this so I´ll try both of them. Just because I´m curious!

Thanks!
// Niklas

Use 'yyyy' instead.

-Steve Huff
http://www.huffs.us

Hi!

Is it possible to use qoutes ("") in a string in VBA?

I´m trying to create a SQL-string to be used to open a recordset but I need
to format some fields in this SQL-string in which qoutes are used. But I get
an error (text = red) when I try this code (below):

strSQL = "SELECT qryValidMemberShip.*, Format([MemberShipEndDate],"yyyy") AS
ValidMemberShipYear" _
& " FROM qryValidMemberShip" _
& " WHERE qryValidMemberShip.fkMemberShipTypeID =" & 5 & " AND
Format(qryValidMemberShip.MemberShipEndDate,"yyyy") =" &
Format(Date(),"yyyy"))"


It highlights the first "yyyy"!

Any idéas of how I solve this?

TIA!
// Niklas

 

[Dirk Goldgar]

Hi!

Is it possible to use qoutes ("") in a string in VBA?

I´m trying to create a SQL-string to be used to open a recordset but
I need to format some fields in this SQL-string in which qoutes are
used. But I get an error (text = red) when I try this code (below):

strSQL = "SELECT qryValidMemberShip.*,
Format([MemberShipEndDate],"yyyy") AS ValidMemberShipYear" _
& " FROM qryValidMemberShip" _
& " WHERE qryValidMemberShip.fkMemberShipTypeID =" & 5 & "
AND Format(qryValidMemberShip.MemberShipEndDate,"yyyy") =" &
Format(Date(),"yyyy"))"


It highlights the first "yyyy"!

Any idéas of how I solve this?

TIA!
// Niklas

Both Allen and Steve have given you good answers to your question. It
may be worth noting, though, that your query in this case might be
rewritten to use the Year function, avoiding the use of quotes
altogether:

strSQL = _
"SELECT *, Year(MemberShipEndDate) AS ValidMemberShipYear " _
"FROM qryValidMemberShip " _
"WHERE fkMemberShipTypeID = 5 " & _
"AND Year(MemberShipEndDate) = " & Year(Date)

That does have the possibly unwanted side effect of returning the
calculated field ValidMemberShipYear as a number, not as a text field,
which may affect how you handle it later on.

 

[Niklas Östergren]

Great!

Thank´s a lot! I´ll try it out since it make the stringe a little bit more
readable.

I´m using it to open up a recordset and to loop through the recordset to
collect values used when I create new record´s (also with DAO.Recordsets).
So it´s no problem if Year return number since I in that case use CDate().
That should worke even on numbers, or does it only work on string?

Thanks for helping out!
// Niklas

Hi!

Is it possible to use qoutes ("") in a string in VBA?

I´m trying to create a SQL-string to be used to open a recordset but
I need to format some fields in this SQL-string in which qoutes are
used. But I get an error (text = red) when I try this code (below):

strSQL = "SELECT qryValidMemberShip.*,
Format([MemberShipEndDate],"yyyy") AS ValidMemberShipYear" _
& " FROM qryValidMemberShip" _
& " WHERE qryValidMemberShip.fkMemberShipTypeID =" & 5 & "
AND Format(qryValidMemberShip.MemberShipEndDate,"yyyy") =" &
Format(Date(),"yyyy"))"


It highlights the first "yyyy"!

Any idéas of how I solve this?

TIA!
// Niklas

Both Allen and Steve have given you good answers to your question. It
may be worth noting, though, that your query in this case might be
rewritten to use the Year function, avoiding the use of quotes
altogether:

strSQL = _
"SELECT *, Year(MemberShipEndDate) AS ValidMemberShipYear " _
"FROM qryValidMemberShip " _
"WHERE fkMemberShipTypeID = 5 " & _
"AND Year(MemberShipEndDate) = " & Year(Date)

That does have the possibly unwanted side effect of returning the
calculated field ValidMemberShipYear as a number, not as a text field,
which may affect how you handle it later on.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Great!

Thank´s a lot! I´ll try it out since it make the stringe a little bit
more readable.

I´m using it to open up a recordset and to loop through the recordset
to collect values used when I create new record´s (also with
DAO.Recordsets). So it´s no problem if Year return number since I in
that case use CDate(). That should worke even on numbers, or does it
only work on string?

I don't quite see where CDate comes into it. CDate does work on
numbers, but maybe not in the way you'd expect. It assumes the number
is a scalar day count from the base date of December 30, 1899. So, for
example,

?CDate(2004)
6/26/1905

The same would be true with the year presented as a string, though:

?CDate("2004")
6/26/1905

So if you want to get from a year number to a date, you have to provide
more information.

 

[Niklas Östergren]

Sorry Dirk!

I was a little bit unclear with my statment!

What I ment is that I could use the number from Year() in a CDate()
expression to convert a valid date. For instance like this:

CDate(Year(MemberShipEndDate) + 1 & "-12-31") => CDate(Year(#2004-12-31#) +
1 & "-12-31") would return date 2005-12-31.

Earlyer have I used Format to retrive current year to be able to produce a
date for next year dynamicly, like this:

?CDate(Format(Date(),"yyyy") + 1 & "-12-31")

But with Year() I could make this line of code just a little bit more
readable thank´s to you Dirk!

// Niklas

 

[Dirk Goldgar]

Sorry Dirk!

I was a little bit unclear with my statment!

What I ment is that I could use the number from Year() in a CDate()
expression to convert a valid date. For instance like this:

CDate(Year(MemberShipEndDate) + 1 & "-12-31") =>
CDate(Year(#2004-12-31#) + 1 & "-12-31") would return date 2005-12-31.

Earlyer have I used Format to retrive current year to be able to
produce a date for next year dynamicly, like this:

?CDate(Format(Date(),"yyyy") + 1 & "-12-31")

But with Year() I could make this line of code just a little bit more
readable thank´s to you Dirk!

You're welcome. But you can do it even more easily than that. Try this
expression instead:

DateSerial(Year(MemberShipEndDate) + 1, 12, 31)

 

[Niklas Östrergren]

Thank´s a lot Dirk!

I´ll use DateSerial instead!

// Niklas