Convert text to number in IIF statement

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

Hi, I have a query where I am using an iif statement so that my field
we either equal text or number depending on the value of another
field. Here is my iif statement

Portfolio1:
IIf([qry_mhfsac4dPassThrough.SecId]="60934n583","FPA",IIf([qry_mhfsac4dPassThrough.SecId]="825252646","Aim",CInt([portfolio])))

I am basically saying that if the SecID of a security is x then"FPA"
or "AIM" otherwise I want to see the portfolio number. However,
whether I use the formula cint() or val() or a couple others that I
have tried, this field still comes out to be text when the last part
of the iif statement is true.

Anyone have any ideas?

Much appreciated,
Brian
 
Hi

This is because the field has to be able to hold text because this is a
possible outcome of your statement.

How does this go on to cause a problem for you?

Regards

Andy Hull
 
Andy - Thanks for your response. I am using an access database to
automate some manual work we used to have to key into Excel. I have a
series of Access queries that export to Excel. This [portfolio] field
is looked up in Excel, where of course numbers are stored as numbers.
I know that I could store 501 as text with '501 but the point is that
I will have about 10 queries exporting and two of them have this same
text problem with the portfolio number. I am trying to make my design
as consistent as possible so that other people can step into it if
need be. See where I am going here?

Thanks in advance,
Brian
 
Hi Brian

I tried the following methods of exporting data to Excel...

1: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_test", _
"c:\test_file.xls"

2: DoCmd.OutputTo acOutputQuery, "qry_test", acFormatXLS, "c:\test_file.xls"

3: DoCmd.TransferText acExportDelim, , "qry_test", "c:\test_file.csv"

Method 1 Results
'FPA
'12345

Methods 2 & 3 Results
FPA
12345

So methods 2 & 3 leave out the single quote. Is this what you prefer?

Possible problems
Method 2 uses old format of Excel which allows a maximum of 16384 rows.
Method 3 is a delimited text file (not .xls) but can be saved as .csv

Note: The above commands have other parameter options which you can
experiment with.

Regards

Andy Hull
 
Andy - I will use option two I hadn't thought of using that output
query, thank you for your help!

-Brian
 
Back
Top