Convert text to number in IIF statement

[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

 

[Guest]

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

 

[Bongard]

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

 

[Guest]

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

 

[Bongard]

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

-Brian