Using the value of i as a field name

[MarionD]

Hi all,

I’m trying to cheat and hoping someone will help me! Cheeky huh!

I have a table with Field names “1” to “31” for the days of the month – now please don’t grouse.. I have to do it this way to allow data to be entered in a cross-tab format. ( I do unroll the data for all other purposes)

I have a record set of this table and am trying to do a .findfirst on the recordset using

For I is 1 to 31
Recs.findfirst “cstr(i) >” & “”

This works fine if I use the field name
Recs.findfirst “[1]>’’”

How can I insert the value of i as the field name, trying to find the first record in the recordset with a value in the field named [1].


Thanks in advance for any help
Marion

 

[modest]

You must seperate variables from the string:

"cstr(" & i & ") >"

But to be better you should just use something like:

"[" & i & "] > "

 

[MarionD]

Hi Modest,
thanks a lot - I'm closer but there yet!

I thought I had to change i to a string.

What I need is :

Set Precs = Me.Form.RecordsetClone
Precs.Filter = "[KT]='P' And [RF]=" & rfs![RID] '** Here I need to add the condition where "[" & i & "]>" nothing i.e. where [1] > ""
Precs.Sort = "[leisnr]"
Set Precs = Precs.OpenRecordset

I just can't get it to work!
Thanks for the time!
Marion

 

[workmad3]

what do you mean by nothing? do you mean a null value in which case use isnull, or if you mean 0, then just put > 0. If neither of these is the case, then i bit more explanation please

 

[MarionD]

Hi there

I mean where there is no value in the field [1]
I have tried isnull("[" & i & "]") and isempty("[" & i & "]") but neither work.
Thanks
Marion

 

[workmad3]

isnull("[" & i & "]") would check if the string "[1]" is null... you need to do the isnull on the entire field.

more familiar with sql, so not entirely sure how 2 get the fieldname in this way. Im sure theres plenty of references on the forum 4 searching though

 

[MarionD]

Yep, This has been my problem from the start. I have searched the forum, but can't find the solution I need.
The problem is getting the filter syntax right with all 3 conditions. If I use only the "real" field name. i.e. isnull([1]) it works, but because I'm looping (For i = 1 to 31)
and the fields I want to check are [KZ]='P' and [RF]=rfs(RID) and isnull([1]) , then in the next loop [KZ]='P' and [RF]=rfs(RID) and isnull([2]) and so on.

thanks again
Marion

 

[Jack Skeleton]

Hi Modest,
What I need is :

Set Precs = Me.Form.RecordsetClone
Precs.Filter = "[KT]='P' And [RF]=" & rfs![RID] '** Here I need to add the condition where "[" & i & "]>" nothing i.e. where [1] > ""
Precs.Sort = "[leisnr]"
Set Precs = Precs.OpenRecordset

but... have you tried???:

Precs.Filter = "[KT]='P' And [RF]=" & rfs![RID] & " And [" & i & "] IS NOT NULL"

If it works pay me a coffee...

 

[MarionD]

Yohoooooo You're brilliant!

Thanks a lot!


Now how do I get coffee from Knittlingen in Germany to Barceloooooooonaaaaaaaaa???

 

[Jack Skeleton]

Brilliant? HAHAHA, if you repeat that 1.000 times more maybe I'll start to believe it's true.

About that coffee... uh... I'm not going on holidays to Germany, so... uh... no, UPS it's not an option... eh... OK, no coffe for the moment, what a pity...