Data Type Confusion

[Jdreyfus]

I have a numerical field that's format is ##-#### much of the time the field begins with zeros, for example: 00-1234. My issue is that I've left the data type for the field as text because it allowed me to enter whatever I wanted, however I have a vb code that doesn't work with a text field, I'm wondering what data type would allow zeros in the format that I need them? Any help would be great.

 

[pbaldy]

AFAIK, no numeric data type will store the leading zeros. You can format them to show them though. What code doesn't work with a text field?

 

[Jdreyfus]

Crap, well I was told that this bit of code wouldn't work:

Private Sub YC_TAG_BeforeUpdate(Cancel As Integer)
If DCount("YC_TAG", "Asset Details", _
"YC_TAG = " & Me.YC_TAG [COLOR=red]<--- this syntax will only work if the [B][U]YC_TAG[/U][/B] is [U]not[/U] a text data type.[/COLOR]) = 0 Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
If MsgBox("This asset already exists in the database. " & _
"Would you like to edit that record?", vbExclamation + vbYesNo) = vbYes Then
me.filter = "YC_TAG = " me.YC_TAG [COLOR=red]<---filters are kind of annoying sometimes.[/COLOR]
[COLOR=red]                                 You can use [B][U]docmd.searchforrecord[/U][/B] and [/COLOR]
[COLOR=red]                                 jump to it with that method instead of filtering if you want.[/COLOR]
Me.FilterOn = True [COLOR=red]<--- if you do as above, you don't have to mess with this line either.  :)[/COLOR]
End If
End If
End Sub
End Sub [COLOR=red]<--- one too many subs here![/COLOR]

 

[Jdreyfus]

Unforunately the field doesn't always lead with zeros, so I think there would be too much confusion format the field so that the zeros appear.

 

[pbaldy]

The DCount will work fine with a text value. Here's a handy reference:

http://www.mvps.org/access/general/gen0018.htm

In many areas of Access/VBA you'll find you need to surround text values with single quotes.