Data type mismatch

[George Too]

Hi all,

My table has a field which requires numerical data entered. I need to enter something like "58971-1". The table is updated through code. This type of data causes the data type mismatch error at runtime.
Is there a way to tell Access that this is a number? Maybe through the use of a function (and which one)?


Thank you,
George

 

[Mile-O]

Two ways:

i) Change your field to a Text data type.

As the hyphen included within your number is textual it might be better to use a text field and use a lookup to check if it has been entered before (or Index it to yes (No Duplicates))

ii) Make an Input Mask for the field such as #####-# and you should have no problems there either.

 

[George Too]

I used to have the field as text but changed it to number as I didn't have that sort of numbers. I took your advise and changed it back to text for a mask would not work since most of the time I wont be using the hyphen, but now I get errors somewhere else in the code.

This is the code I use mainly to get my subform to display just the necessary records based on cmbProject:

Set rst = D.OpenRecordset("tblColorsHigh", DB_OPEN_DYNASET)

Criteria = "[Project] = '" & "[cmbProject]" & "'"

rst.FindFirst Criteria

Brand = Me.cmbBrand
If InStr(1, Brand, "Breakfast Blend") Then
Me.childHigh.SourceObject = "subfrmColorsHigh_BrkfstBlnd"
'Filter
[Forms]![frmMain]![childHigh].Form.FilterOn = True
[Forms]![frmMain]![childHigh].Form.Filter = "[Project] =" & [cmbProject]
[Forms]![frmMain]![childHigh].Form.Requery
end if

I get an error "2001" which says that I canceled the previous operation and hilights the line: [Forms]![frmMain]![childHigh].Form.FilterOn = True

Playing with the filter for the subform eliminates the 2001 error but the form won't take the filter: [Forms]![frmMain]![childHigh].Form.Filter = "[Project] =" & [cmbProject]

It use to take it before when the table field was set as a number.

What gives?

Thanks,
George

 

[rockman]

For a start I believe you have an error in this line:

Criteria = "[Project] = '" & "[cmbProject]" & "'"

Perhaps it should be:

Criteria = "[Project] = '" & [cmbProject] & "'"

HTH,
Jeff

 

[George Too]

You are absolutely right rockman, I realized it after posting the code and went throug it.

Thanks for posting though.

Regards,
George