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)