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"),"qryOTAllowanc e")

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.Defa ultValue" 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]

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).

There seems to be a misunderstandin g 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.