Need variable field names in SQL query 1

[georgesOne]

Dear All:
I have a select query, which at one part needs variable vield names via a vba function.
The following query works:
Select A.ID, A.MS200810, A.MS200811, A.MS200812, IIf(A.MS200812 = A.MS200810,"","X") As GOMS
However the query:
Select A.ID, A.MS200810, A.MS200811, A.MS200812, IIf(bss_fGetDate1() = bss_fGetDate2(),"","X") As GOMS
does not work.

bss_fGetDate(1) and bss_fGetDate(2) are defined by VBA:

Function bss_fGetDate(1) As String
bss_fGetDate(1) = "A." & DFirst("MSLastDate", "atblDates")
End Function

Function bss_fGetDate(2) As String
bss_fGetDate(2) = "A." & DFirst("MS1Date", "atblDates")
End Function

and produce "A.MS200812" and "A.MS200810" respectively.

I understand that the query now compares the two strings "A.MS200812" and "A.MS200810" and correctly concludes the result is false (and "X" is what I see in all table rows). I believe the error comes from my function definition as String, but I do not know how to get the query understand that these are no strings but field names.

Thank you for any comments, georgesOne

 

[georgesOne]

Sorry the queries are of course

SELECT A.ID, A.MS200810, A.MS200811, A.MS200812, IIf(A.MS200812 = A.MS200810,"","X") As GOMS FROM tblMiles AS A
and
SELECT A.ID, A.MS200810, A.MS200811, A.MS200812, IIf(bss_fGetDate1() = bss_fGetDate2(),"","X") As GOMS FROM tblMiles As A

Thanks, georgesOne

 

[dhookom]

I would use DAO code to change the SQL property of a saved query.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Thanks for the suggestion and quick response, Duane.
I understand and will try - unfortunately the query is already very complex (and will become more complex having many interdependant fields - I needed to separate it in three queries to meet Access field limits) - more than 12000 letters (yes, some are redundant) - so there are many chances to make mistakes.
Actually, I am already crazy right now - if you read the news about a sadly ended 'programmer' in a closed institute in Japan, it is me.
May need to reconsider the whole thing...
Nevertheless - gambarimashoo, georgesOne

 

[dhookom]

It looks to me like you are storing data in field names like "MS200812". This quite often leads to overly complex results. I could be wrong but having to dynamically select field names suggests issues.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Hi Duane,
Actually I am on my way... your suggestion works (in a smaller test db). Thank you very much!
Probably you will not read the sad news.
Kind regards, georgesOne