Force .DefaultValue to be a string

[kstevens]

I have a sub to determine if overtime has come into play yet on a certain job. If (preapproved) overtime has started then the next (subform) record can automatically be populated from a query checking the overtime allowance. Here is the code.

Private Sub OT_Test()
If Forms!frmJobHourCostEmpName.TotalHours = 8 Then
Me.OTJob.DefaultValue = True
Me.BilledHours.DefaultValue = DLookup("OTApproval", "qryOTAllowance")
Me.JobNumber.DefaultValue = DLookup(CStr("JobNo"), "qryOTAllowance")
Me.BilledPercentage.DefaultValue = BilledHours / (8 + Nz(Forms!frmJobHourCostEmpName.OTHours, 0))
End If
End Sub

The issue is that Job Numbers are in a format of:

Example: "12345-1" or "12345-2" or "65487-1"...ok you get it.

you may have noticed the DLookup(CStr("JobNo"),"qryOTAllowance")

The actual field in the query is JobNo: Cstr(Jobnumber)

I even tried

Dim JobNumberTemp as string
JobNumberTemp = DLookup(CStr("JobNo"), "qryOTAllowance")
Me.JobNumber.DefaultValue  = JobNumberTemp
 

In hoping that the "JobNumber.DefaultValue" would remain a string ("12345-2") instead of a math problem (12345-2 = 12343) because all that i can get to display is "12343" I noticed that if i dont use defaultvalue, i get the actual string, but obviously on the wrong record (the current record, instead of the next record)

Does anybody have any ideas of how to accomplish this? Its driving me nuts. As always..... Thanks for any help.

[FishVal]

Does it help to add text delimiters to the string assigned to DefaultValue property?

Regards,
Fish.

[ChipR]

Try

Me.JobNumber.DefaultValue  = """" & JobNumberTemp & """"

[kstevens]

Thanks ChipR and FishVal i never tried it, because originally i thinking about 1 set of quotes which would have been a literal sting but i never thought about """" & so thanks. It worked great.

[NeoPa]

Would it not work if the CStr() call were outside of the DLookup(), rather than within it?

[missinglinq]

I'm really confused here! If the values in this field are in a format of:

"12345-1" or "12345-2" or "65487-1"

then the field is defined as Text. You cannot enter these values in a field that is defined as a Number datatype.Why do you need to use CStr()? Using Chip's suggestion alone should do it.

Linq ;0)>

[NeoPa]

Quite right. I skimmed over the penultimate paragraph in the OP too quickly it seems.

In that case Chip is indeed on the ball (again).

I would do it very slightly differently for reasons explained in Quotes (') and Double-Quotes (") - Where and When to use them.

Me.JobNumber.DefaultValue = "'" & Me.JobNumberTemp & "'"

[kstevens]

I did use Chips solution and it did work. Originally the query was doing the math problem instead of correctly reporting it as text. Then (only when i set .defaultvalue = tempjobnumber) it was still doing the math. I havent had a chance to use NeoPa's advice because once it worked.... I stopped, lol.

OFF TOPIC Can you hijack your own thread? :)
I never usually use

""""

i almost always use

"'"

although i know it will get me in trouble one day ( actually it already has with an emplyees last name - O'Bryant). I REALLY struggle when it comes to text delimeters, i have read the tutorial on here about it, but it doesnt really help me.....(well it does with individual fields, but when i try to do

docmd.openreport "rptWhatEver",acnormal,,"stuff='" & text & "'" and anything else
 

with multiple criteria, the confusion really begins.

and sometimes i even have to change that to

docmd.openreport "rptWhatEver",acnormal,,"stuff=" & "'" & text & "'" and anything else

And i havenet really quite figured out why... i tend to use the first one most of the time, now that i think about it, iam not really sure which one ends up giving me the problems.

[NeoPa]

Such problems can occur either way in fact.
EG. {O'Brien}
{4" by 2" plank}.
" is a sign for Inches if you weren't aware of that.

Doubling-up quotes in the data is generally a way to get past this.

The correct (standard) character for quotes in SQL strings is ('). Access uses (") by default instead (M$ were stupid enough to think that this would simplify matters rather than the fairly predictable result of confusing people greatly). In Access either will work though (at least until ANSI-92 becomes the default, which is unlikely to be far away).

To program such problems properly, it is advisable to pass any such references through a filter function so that any such quotes get doubled for you automatically if they occur.

I hope this helps and clarifies things for you a bit.

[ChipR]

The best way to figure this out is to use an intermediate variable so that you can check the value of the string. This way, you will quickly see where you made a mistake.

Dim strCriteria as String
strCriteria = "[field1] = """ & variable & """"
MsgBox strCriteria    'or Debug
DLookup("field2", "table1", strCriteria)

[NeoPa]

That's certainly a sensible approach for the development stage. Less necessary for when the code is perfected.

Personally I use the debugger (Debugging in VBA) to see the values of variables, but anything that works for you is good. The important thing is to have access to the information when developing. Either method produces this.

I cannot stress too much how much help debugging techniques can be while developing your projects. A very good point to raise :)

[HankWalters]

I don't know if this the amateur way of doing it or not (or maybe just the lazy way), but I like to deal with quotes like this:

Dim strCriteria As String
Dim ChrQuote As String
ChrQuote = Chr(34)  ' Chr(34) is the character code for a quote sign
strCriteria = "[field1] = " & ChrQuote & strVariable & ChrQuote
DLookup("field2", "table1", strCriteria)

That way, you don't have to worry about the double-quote and single-quote (Chr(39)) rules, you can just string variables together like you want.

It also works with message box messages when you want to include a CR/LF (Chr(13) and Chr(10) respectively).

[ChipR]

Yes, that does work. I thought about it, and would have written that in my original post, but I can never remember Chr(34). I often use vbCrLf, maybe they should make a vbQuote.

[kstevens]

Anybdoy have any issues with Chr(34)? Sounds easy. Is there a difference between vbCrLf, and vbCr?

*Edit* I was working on my project came back across the name O'Bryant, and was wondering.... with the Chr(34)... wouldnt that fall into the same single quote trap?

[NeoPa]

@HankWalters
There seems to be a misunderstanding here.

This actually gains you nothing, as the same rules apply whichever way you produce the quotes.

All you have done is made the code less easy to read and understand.

As far as CRs (etc) go :

vbCrLf --> Carriage Return + Line Feed --> Chr(13) & Chr(10)
vbCr   --> Carriage Return             --> Chr(13)
vbLf   --> Line Feed                   --> Chr(10)

Again, I see no benefit in obscuring what you are doing by using a Chr() call.

[NeoPa]

@ChipR
As They in this case would be the same people that caused the confusion in the first place I doubt they would even use the correct one.

[ChipR]

That was my first reaction, but I tested out composing strings with Chr(39), and it allows you to assign strings with quotes in them properly.

[NeoPa]

@kstevens
Historically, Carriage Return returned the carriage of a teletype or typewriter type device to the left hand side. A Line Feed fed a line of text.

When the devices were still mechanical, it was necessary to send them in that order so that the Carriage Return started earlier. It took longer and the Line Feed could be executed while the carriage was still in transit.

More recently, it is standard on a PC using any form of windows that the text files separate their lines with both characters. However, Unix and other Nix OSes have a standard where the text lines are separated with the Carriage Return character only. Sometimes you may deal with vbCR, and others with vbCrLf.

Technically, the enter key is actually returns the vbCr character, but this is often translated on the fly in Windows to both characters (vbCrLf).

[NeoPa]

@ChipR
Perhaps you could clarify what you were referring to here. I'm afraid I may have obscured the conversation by posting between.

I can't actually think of a scenario where that usage helps anything.

[ChipR]

Never mind on that last post! That's what I get for not testing well. Obviously Chr(34) and """" both let you compose strings with quotes in them and assign them to variables in vba. It's when you try to use those strings with SQL functions that they both fail.

[NeoPa]

@NeoPa
Indeed Chip.

As this is the main crux of the problem here, I perhaps ought to flesh out somewhat what I was saying earlier. The function could be :

Public Function DoQuotes(strField As String, _
                         Optional ByVal strQuote As String = "'") As String
    DoQuotes = Replace(strField, strQuote, strQuote & strQuote)
End Function

This allows for usage with either quote character. You can even choose to change the default to what you are most comfortable with yourself.

Now, assuming you have a TextBox control on your form that you wish to use as a filter for some SQL, you might use code similar to this to make use of it :

...
strSQL = "SELECT ... " & _
         "FROM ... " & _
         "WHERE [YourField] Like '*" & DoQuotes(Me.TextBoxControl) & "*'"

[NeoPa]

A new thread (SQL Injection Attack Discussion) has been created to handle the discussion of SQL Injection Attacks.

It is related to the original but not too directly.

[Stewart Ross]

Could I just mention with regard to the very first post in this thread - about the default value issue - ALL the lines in the code snippet which refer to the DefaultValue property are incorrectly formed. Regardless of the type of the value you wish to set, the defaultvalue property is a string. This does indeed mean ensuring that quotes are used to enclose such values - which is what Chip's solution did. ALL the ones shown in the code snippet should be set inside quotes accordingly - so

xxx.defaultvalue = true

should really be

xxx.defaultvalue = "'" & true & "'"

although the VBA interpreter will do its best to convert values on the fly to match the string type of the defaultvalue property.

What happened in this particular case (the 12345-1 etc issue) is that without the quotes the VBA interpreter treated the default value setting as an arithmetic expression involving two constants, which it replaced with the result of the expression. If SomeVar represents a variable with a text value of "12345-1", then writing

fieldname.defaultvalue = SomeVar

will result in the interpreter substituting the value of SomeVar as an expression

12345 - 1 (result 12344)

This is quite different to setting it correctly inside quotes:

fieldname.defaultvalue = "'" & SomeVar & "'"

resulting in the default value showing correctly as 12345-1 in the control.

An extract from the MS help entry gives an example, though unfortunately not one relating to expressions:

In Visual Basic, use a string expression to set the value of this property. For example, the following code sets the DefaultValue property for a text box control named PaymentMethod to "Cash":

Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

-Stewart

[HankWalters]

@NeoPa
As far as CRs (etc) go, I totally agree with you. I was using those character codes back in Access 2.0 where I don't think (but I could be wrong) there was a vbCr, etc.

As far as quotes go, I guess it is just what I am used to but these two lines produce the same results:

strCriteria = "[field1] = """ & variable & """"
strCriteria = "[field1] = " & ChrQuote & variable & ChrQuote

To me, the second one is actually easier to code and to understand because you don't have to worry about when to use triple or quadruple quotes. Of course, the rules about quotes in the result string are the same, I just meant you don't have to worry about when to use """ or """" or whatever. Maybe its just me, sorry if I confused things.

[FishVal]

@HankWalters
It could be done either way which seems suitable, beautiful or even the only right one for a particular coder .

Additionally, Access.Application class has BuildCriteria() method which build criteria expression with "appropriate" (quotes because it uses double quotes to introduce string constant into resulting expression <uuuuf>) delimiters.

However, all those methods have nothing to do with delimiter symbols already existing in criteria. Approach suggested by NeoPa to preprocess string with a simple VBA code is, IMHO, very suitable solution.

[NeoPa]

@HankWalters
Hank,

Let me start by saying that, not only do you have every right to post your thoughts, however right or wrong they are, but we always appreciate any input, such as yours, which is intended to be helpful or share your experience. This doesn't mean that we won't necessarily post comments in response. Just that any such responses are part of a discussion where any or all can make their own particular views known. That way we all pick up ideas, or ways of looking at things, that we may not have considered before.

Those of us here who answer questions are always picking up new things from others. Speaking for myself I know this happens regularly.

Further comment on your latest post :

You referred in your earlier post to Chr(34) rather than ChrQuote. Either way, it does make it clearer you're dealing with quotes, but when you're reading the code you're not generally looking just for that. You want a good understanding of the whole picture. If the focus is so much on the quotes it makes reading the string more difficult (to my mind at least).

As for doubling-up the quotes when working on SQL within VBA - This is generally unnecessary. As Access VBA uses the Double-Quote and the SQL standard is to use Single-Quotes there should not be any issue. Using these correctly also makes working in such code so much easier to read and understand. If people insist on using the Double-Quotes within SQL this can still work, it just makes things more complicated.

Just to finish by repeating the most important point - You have every right to express your opinion, and we value all contributions, whether they fit in with our own views or not.

Welcome to Bytes!