Search results

Hi everyone, I have the following simple question. How can I restrict the field entries in a table to a few selected values? For example, I have the field name 'Month' and would like to restrict the entries to: January, February, March, etc... In the table I would then like to have a drop-down...

I think I need to clearify. The '0' is a faulty entry. It actually means that there is no value for that field. I know that this should be changed in the original database but this is the format in which the data is received (I've got to work with it as is). I'm going to experiment a bit with...

It is defined as integer. Is there any way to test for integer = 0 in field?

This is my VBA code for the commandarray. The code works if I don't implement the IIF function for Performance.FundsManaged. (see red font) but rather import the data as is. There must be something wrong with this function. It works in Access sql. I'm not very sure-footed when it comes to the...

Thanks for your response, two problems though: First, the query is used in a VBA script to export data to an excel pivottable. Unfortunately, Nz() is not recognized as a valid function in this context. The workaround is an IIf function. Second, Nz() would check for empty cells (null values)...

hi everyone. I would like to know the correct syntax for the following Iif function: Select IIF([Performance.Return]='0','') As Performance I would like get the return datafield from table 'Performance' and integrate it as a column in my query. However, whenever the datafield is null, I...

No, the performance data should be stacked: ROI AUM ROI AUM etc... thus for every Fund-Date combination there will be two region entries, one for ROI and the other for AUM. I realize that the tables and queries contain a lot of duplicate data but as I said, I have no impact on the format of said...

This is what I've come up with. Let's assume the first table (Information) looks like the following: Fundname, Fundcode, Europe, America, Asia, Other Fund 1, 101, -1, 0, 0, 0 Fund 2, 102, 0, -1, 0, 0 Fund 3, 103, 0, 0, -1, 0 Fund 4, 104, -1, 0, 0, 0 etc... Again, this...

I think that's what I will try. I'm not worried about updating the query. I intend to embed it in a VBA script. Thus, it will re-run every time the database is being accessed. Not the most elegant way but faulty database design in this case wasn't my fault...Thanks for your help, I'll report...

I couldn't agree more. The format you are suggesting is exactly what I'm aiming for. However, I'm not the administrator of the database. Thus, any manipulation of the data can only be indirect through queries or macros. I'm aware that the data is presented in a nonsensical way but this is how...

Hi everyone, I have a problem regarding the presentation of data in one of the tables of my database. Let's assume for simplicity's sake the table looks like the following (Fundcode could be the unique primary key): <Fundname> <Fundcode> <Europe> <America> <Asia> <Other> <Fund 1> <101> <-1> <...

I know,but i cant seem to shut this one down. Just ignore this thread, I accidentally submitted it twice...

I have narrowed down the problem even further. I am using a OLEBD connection to import the data into excel pivottable. As it seems the IIF(IsNull()) combo cannot be used here (although no error message pops up). It just returns zero values for all entries (apparently misinterpreting the IIF...

Hi everyone, I am running a query in an access database that consists of three tables labeled: ASSETS, RETURNS, ADMINISTRATIVE. The query looks as follows: SELECT RETURNS.Codes, RETURNS.Fundname, ADMINISTRATIVE.Main_Strategy, RETURNS.MM_DD_YYYY, IIf(IsNull([ASSETS.Assets])...

That did the trick! Many thanks :)

Hello everyone, I would like to run the following query from two tables in my database: SELECT RETURNS.Fundcode, RETURNS.Fundname, RETURNS.MM_DD_YYYY, RETURNS.ROI, ASSETS.AUM FROM RETURNS LEFT JOIN ASSETS ON RETURNS.Fundcode = ASSETS.Fundcode AND RETURNS.MM_DD_YYYY = ASSETS.MM_DD_YYYY...

I didn't know you could use the IIf function nested in a SQL query. Including the IIf function in the select line does the trick. thanks MsAccessRookie

ps. without simply back-up saving the database

Hello everyone, I have a table within my database that denotes of whether or not a particular investment fund employs leverage. It is, however, not a YES/NO field. The datatype is text. The field values are: - No - Yes - Yes(10%) - Yes(50%) - Yes(80%) - Yes(FOREX) I want to create a query...

Thanks everyone!