Sarah,
1. Understand. I would still strongly recommend against storing computed
values in a table.
2. I have a function that I use to wrap text values in quotes (either
double or single), so I don't have to worry about whether to use three or
four or whatever. This function allows me to pass a variant TextToQuote (the
variant was originally designed to accomodates NULLs, but with the addition
of the WrapWith parameter I can now pass it either quotes or an apostrophe,
or if I want to format text dates with a # wrapper, I can do that as well.
It also accepts parameters for replacing a set of characters with another set
of characters. This is useful when I want to wrap something in double
quotes, but have an incling that the text (usually some sort of comment
field) might contain a quote. In this case, I automatically replace the
quotes with an apostrophe.
To use this you might do:
Quotes([TextField], "'") to wrap it in single quotes, or
Quotes([TextField], """") or Quotes([TextField], chr$(34)) to wrap it in
double quotes.
Public Function Quotes(TextToQuote As Variant, _
Optional WrapWith As String = """", _
Optional TxtToReplace As String = """", _
Optional TxtReplaceWith As String = "'") As String
Quotes = WrapWith _
& Replace(Nz(TextToQuote, ""), TxtToReplace, TxtReplaceWith) _
& WrapWith
End Function
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
Sarah said:
Dale thanks for your time in asking the questions. Ofer Cohen figured it out
for me. I didn't give you the full sql statement since I didn't want to
overwhelm anyone with the length of it. There's a lot going on to this
scenerio than what you see. I just really needed to figure out the quote
syntax.
Thanks again for your time!
Dale Fye said:
Sarah,
I guess I should have gone back to the age old question, why are you
inserting a computed value in a field? There are very few good reasons to
store a computed value, and lots of reasons not to.
Since I don't know your level of expertise, I'm going to ask a series of
questions. Some of them may seem pretty simple, but I have found that when I
have a problem, and go back and address the questions at the most basic
level, that I usually find my mistake.
1. Why do you want to build this query at run time rather than just
creating and saving the query? There are no parameters in this query that
would prevent you from doing this. Not only does that make more sense, but
the query would run faster since it would already be compiled. Did you build
this query in the query grid, and then copy/paste it into your code? If you
do that, you can use regular quote symbols, and don't have to worry about
wrapping things. If you did this, did it work properly from the query grid?
2. Why does your query include the FuncDode table, it doesn't show up
anywhere in the query except in the JOIN clause? It appears that that table
is unnecessary?
3. Have you confirmed that that query actually has a [TheStation] field?
Can you post the code for qry01SumJPHCPTT?
4. What error are you getting?
Dale
--
Email address is not valid.
Please reply to newsgroup only.
:
That's not it.
:
Sarah,
Actually, I think your problem is that your syntax, I think you have a ")"
in the wrong place. I think it should read:
Round(Nz(DSum('TheMax', _
'qry01SumJPHCPTT', _
'TheStation='" & [StationNo] & "'), 1)) AS F22
HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.
:
I have this append query that works in an sql query, but does not work when I
run it in code on a form.
docmd.runsql "INSERT INTO SDP ( MTMID, Station, F22 )
SELECT Seq.MTMID, Station.StationNo, Round(Nz(DSum('TheMax',
'qry01SumJPHCPTT', 'TheStation='" & [StationNo]) & "'), 1) AS F22
FROM ((MTM INNER JOIN Seq ON MTM.MTMID = Seq.MTMID) INNER JOIN Station ON
MTM.MTMID = Station.MTMID) INNER JOIN FuncCode ON Station.StationID =
FuncCode.StationID;"
The problem is:
'TheStation='" & [StationNo]) & "'
StationNo is a text string. So I have to encase it in quotes, but when I
put it in a form code I get an error. Any suggestions on another way I can
encase StationNo in code without using the double quote.
Thanks,
Sarah
