Hello,
I have a 2 fields/2 tables/2 data types. Using FormatNumber(FIELD) I've converted the fields to a number type in my query however I'm getting a type mismatch error. Any ideas?
Thanks,
Hello,
I have a 2 fields/2 tables/2 data types. Using FormatNumber(FIELD) I've converted the fields to a number type in my query however I'm getting a type mismatch error. Any ideas?
Thanks,
Format() and FormatNumber() actually result in a string value, not a true number. Why do you need to format in query? Apply formatting with property in textbox on form or report.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
If you need to do math using a field, you could/should change the field type to a Number (Integer, Long Integer, Single,...)
An alternative is to use one of the type conversion functions: CInt(Field), CLng(field), etc. See Access Help.
June7, I need to convert both to number in order to do math against the two fields and I don't want to make a change to the table because I'm using the saved import feature which will break my changes everytime I refresh.
Thank you Steve. I will try using those conversion functions.
Then use conversion functions as shown by ssanfu. However, be aware that number conversion functions will error on Null, alpha or empty string. The null can be dealt with:
CLng(Nz([field],0))
Of course, if you don't want the records with null to be aggregated (average, count), will defeat that because Nz() in this case will return 0.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7, thanks for that. I'm just curious what to do in the case where there are actually alpha values mixed into the column. Is there a way to ignore them when I convert everything else to a number?
The pitfalls of bad data.
Would the alphas be part of the value? Val() function will pull numbers from the value until it hits a non-number character except period.
Val(Nz([field],0))
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7, I empathize with your emoticon. Just my luck to have this type of data! No the alphas are mostly "N/A" I think. Any ideas?
The Val() function would return a 0 for "N/A".
If those are the ONLY alphas, could run an UPDATE sql action to remove the "N/A" values and replace with Null or 0.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
A possible alternative would be to write an UDF to return the numerical part of the field.....