Trying to populate fields in a database with a query not working.

[dopy]

Hi all, I'm having some issues with populating a column in a database with a query.

The idea is that it would fill in certain terms depending on a value of another column in said database that is also populated by a query.

I've managed to get said column populated by a query which subtracts 2 dates found in said database and populates the result in said column.

However, now I'm trying to add another column that would turn said results into a specified term.

It works, somewhat, however it seems to randomly mix and match what term it wants to place in said field.

I've tried the following query criteria:

IIf([Database]![Aantal maanden sinds laatste verslag]="999";"Dossier afgesloten";IIf([Database]![Aantal maanden sinds laatste verslag] Between "0" And "12";"Normaal";IIf([Database]![Aantal maanden sinds laatste verslag] Between "11" And "999";"Jaarverslag buiten termijn";"")))

and

IIf([Database]![Aantal maanden sinds laatste verslag]="999";"Dossier afgesloten";IIf([Database]![Aantal maanden sinds laatste verslag]<="12";"Normaal";"Jaarverslag buiten termijn"))

However they both give me the same results.

Here's an image:
tinypic(dot)com/r/miie4j/9

Notice how it randomly gives a 5 the term "jaarverslag buiten termijn" when it should be "normaal", same with the 3, meanwhile it gives 106 a "normaal" when it should be "jaarverslag buiten termijn".

Please help!

 

[Minty]

I think you should rationalise your field names - remove the spaces and shorten them, do you really have a table called Database ?

To your problem - This <="12" means you are trying to perform maths on a text string. What result do you think you will get?
If you open the immediate window (Press ctrl+G) and type ? "202" > "21" see what Access thinks...

 

[dopy]

I think you should rationalise your field names - remove the spaces and shorten them, do you really have a table called Database ?

To your problem - This <="12" means you are trying to perform maths on a text string. What result do you think you will get?
If you open the immediate window (Press ctrl+G) and type ? "202" > "21" see what Access thinks...

I've done so and also removed the equals sign, I'm not sure why/when I put that there, that obviously doesn't make sense.

If I change the column in my database to a number field instead of text, it doesn't run the query on any field at all.

 

[Minty]

So what is your query IIf looking like now ?

 

[dopy]

So what is your query IIf looking like now ?

IIf([Aantal maanden sinds laatste verslag]=999;"Dossier afgesloten";IIf([Aantal maanden sinds laatste verslag]<12;"normaal";"Jaarverslag buiten termijn"))

Is my code right now, which is set directly in the database as a calculated field, which does seem to work, is this correct or is there another way I need to be doing this?

 

[Minty]

No that looks okay to me, I thought from your comment that it wasn't working. Sorry for the confusion.

 

[dopy]

No that looks okay to me, I thought from your comment that it wasn't working. Sorry for the confusion.

Oh yeah no it wasn't working, as I had that code as a separate query, not set up as an expression in the database itself (I'm terribly sorry if I'm using the wrong terms, this is literally my first time working with anything like access).

It didn't work as a query, so I edited it a bit and put it directly into the database expressions.

 

[Minty]

Is this a calculated field in the Table?

 

[dopy]

Is this a calculated field in the Table?

Yes it is.