Recordset Data issue with dashes

[DavisDS]

In Reference to thread705-22237 I have a similar problem.

I Use a data field containg strings with dashes, A social
security number for example.
With a Social the solution seems simple:
break the string and concat it with the '-'.

I also use other numbers with inconsistant lengths such as
123-123-12
123-12-123
123-123-1234
When I pull the value from a table into a record set these would translate to:
12312312
12312123
1231231234
I NEED THESE DASHES

Obviously concating the string wouldn't work because the lengths between the breaks are inconsistant.

Is there a way to FORCE a query to return a string INCLUDING the dashes?

 

[Zion7]

Change your field to Text data type, ...assuming it's currently Number?

 

[DavisDS]

The code:
Private Sub TitleList_Change()
Me.Selected.Value = Me.TitleList.Value
Dim oRS As Recordset
Set oRS = CurrentDb().OpenRecordset("SELECT * FROM Missions WHERE Display = '" & Me.Selected.Value & "'")
If Not oRS.EOF Then
Cont = oRS.Fields("Control")
MsgBox oRS.Fields("Control")
POC = oRS.Fields("POC")
ReportTo = oRS.Fields("ReportTo")
Loca = oRS.Fields("Location")
Recur = oRS.Fields("Recurring")
Daily = oRS.Fields("Daily")
Report = Format(oRS.Fields("ReportTime"), "hhmm D MMM YY")
Releas = Format(oRS.Fields("ReleaseTime"), "hhmm D MMM YY")
'Report = oRS.Fields("ReportTime")
'Releas = oRS.Fields("ReleaseTime")
Mission = oRS.Fields("Mission")
Instruct = oRS.Fields("Instructions")
Miles = oRS.Fields("Miles")
Else
Cont = ""
POC = ""
ReportTo = ""
Loca = ""
Recur = ""
Daily = ""
Report = ""
Releas = ""
Mission = ""
Instruct = ""
Miles = ""
End If
oRS.Close
Me.Control.Value = Cont
Me.POC.Value = POC
Me.ReportTo.Value = ReportTo
Me.Location.Value = Loca
Me.Recurring.Value = Recur
Me.Daily.Value = Daily
Me.Report.Value = Report
Me.Rele.Value = Releas
Me.Mission.Value = Mission
Me.Instructions.Value = Instruct
Me.Miles.Value = Miles
End Sub

"Control" in the table is a text field with a length of 50

The issue is not the data type

 

[Zion7]

What data type is Cont?

"I Use a data field containg strings with dashes..."

at what point are the dashes getting omitted?

Here, Cont = oRS.Fields("Control")
MsgBox oRS.Fields("Control")

or here, Me.Control.Value = Cont

 

[DavisDS]

The First one, When The recordset pulls from the table it reads the string with dashes omitted

 

[missinglinq]

You state

I also use other numbers with inconsistant lengths such as
123-123-12
123-12-123
123-123-1234

which can't be true, as data with dashes in it aren't numeric. So the values above have to be strings. If they're strings, the dashes should be stored in the table and hence retrieved.

When this data is originally entered, are you using an input mask?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Zion7]

I was thinking an Input mask, or formatting also missinglinq, but due to the inconsistency of the data format, is this possible?

David, you didn't say what Data type Cont is?
If variant, may be converting to Integer?

When looking at raw data from table, are dashes included?

 

[missinglinq]

I don't know any other way, unless the developer has coded to remove the dashes, for them to disappear from strings, except thru imput masks with the table storage for set to omit the dashes. They're not the same, i.e. not the same number of digits, but they could all still come from input mask, I think, with maybe with some digits not required.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[DavisDS]

Cont is a String by the way

for my Socials my input mask is 000\-00\-0000
for my Control Number there is no input mask as the input varies

So when the recordset recieves the value from the database it pulls a String and Passes it into a variable String which is in turn placed in a form field. When the recordset reads the database it trunks the - before storing the value.

The MsgBox is my debug outputting WHAT EXACTLY the value being read is as it pulls from the table the result is a String of Numbers instead of a String with numbers and dashes

 

[DavisDS]

figured it out!!

When I created the "control" field I mirrored the social specifying an input mask: 000\-000\-0099;
There inlies the problem.

When using an input Mask VBA Ignores any Masked characters

Example:

In a date structure DD Mmm YY -> 21 Jan 07
As the String "21 Jan 07" passed to a field with no Input mask the value remains uneffected.

But when using the input mask 00\ >L<LL\ 00; To force the proper format....
The user PHYSICALLY types: 21Jan07
The mask creates the format: 21 Jan 07

When This is passed through VBA as variables, recordsets, etc:
Only the PHYSICAL entry is stored and the masks formatting (The Spaces) is ignored!

Removing the mask copmletely in the contol case eliminates the issus because the dashes have to be typed by the user

 

[Zion7]

Thank-you for that David!

...I also believe when creating the input mask,
that Access gives the option of
storing the Data with the InputMask format, or not.

 

[missinglinq]

I was just going to tell him that! For SSN, telephone numbers and extended zipcodes (i.e. 23230-1234) Access gives you the option of storing the data with or without the mask characters!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!