Can I call a function from a query?

[Steff_DK]

I have a function fWin2KUserName() that dims strUserName.

Can I (and how) use that in a SQL sentence like; WHERE [klcv_nr]=strUserName???

 

[Steff_DK]

Works perfectly!

How do I do the same with the the table names?
i.e. dim the table name in a function, and put it after SELECT..

Lets say the function is tblname()...

Will "SELECT tblname().[this_field]..." work?

 

[Steff_DK]

Okay. Do I do this in the form load event, or in a button?
Can it be done like so:

Let's say i have a button:

When it is clicked I:

define the vars
create a sql based on the vars
open the form in which the data should be presented

-or is it usually done in another way?

 

[WayneRyan]

Steff,

Use the Search Facility here and look for "QueryDef", you'll find quite a few
samples.

Wayne

 

[Steff_DK]

QueryDef pointed me in the right direction - thanks

However, when I dim db as DAO.Recordset I get that error 'not defined'...

I know I have to refer to uh.. something but what/how/when/where

 

[WayneRyan]

Steff,

That's a reference problem.

Get your code in Design View; Select: Tools --> References; then check
the reference for Microsoft DAO.

Your code should have:

Dim dbs As DAO.DataBase
Dim rst As DAO.Recordset

Wayne

 

[Steff_DK]

This query is almost working...
Only it doesnt seem to care about the "WHERE"... I also get posts where [klcv_nr] is NOT fWin2KUserName() or ""...

What's up?!?

SELECT DISTINCTROW [tbl_chk_FA].[klcv_nr], [tbl_test_FA].[test_id], [tbl_test_FA].[komp_omr], [tbl_test_FA].[emne], [tbl_test_FA].[underpkt], [tbl_test_FA].[kvartal], [tbl_test_FA].[metode], [tbl_test_FA].[reference], Max([tbl_chk_FA].[dato]) AS [Max Of tbl_chk_FA_dato]
FROM tbl_test_FA LEFT JOIN tbl_chk_FA ON [tbl_test_FA].[test_id]=[tbl_chk_FA].[test_id]
WHERE [tbl_chk_FA].[klcv_nr]=fWin2KUserName() or ""
GROUP BY [tbl_chk_FA].[klcv_nr], Year([tbl_chk_FA].[dato]), Format$([tbl_chk_FA].[dato],'yyyy'), [tbl_test_FA].[test_id], [tbl_test_FA].[komp_omr], [tbl_test_FA].[emne], [tbl_test_FA].[underpkt], [tbl_test_FA].[kvartal], [tbl_test_FA].[metode], [tbl_test_FA].[reference]
ORDER BY [tbl_test_FA].[test_id];

 

[WayneRyan]

Steff,

WHERE [tbl_chk_FA].[klcv_nr] = fWin2KUserName() or
[tbl_chk_FA].[klcv_nr] = ""

Wayne

 

[Steff_DK]

Okay, I put in the "" beacuse I also wanted to get all the records from the table, tbl_test_Fa, even where there where no corresponding [klcv_nr] & [dato] in the table tbl_chk_FA.

Now I only get the post if there's a corresponding entry in tbl_chk_FA.

tbl_test_FA holds all the tests a given person hav to take.
tbl_chk_FA holds the "check marks" for when they took their test.

I want a list of the tests, and then if there is a check mark for that person, to display that ([klcv_nr] is their logon and [dato] is the date)

I want query result to just leave the fields empty - but still display them - when there are no entries for a certain record in tbl_chk_FA,

i.e. Display the entire contents of tbl_test_FA, and then, when/if there is a corresponding entry in tbl_chk_FA to display the values of that [klcv_nr] etc.

 

[WayneRyan]

Steff,

OK, I think I see now, you want the record that matches the function's
return value, or all records if the function returns "".

WHERE [tbl_chk_FA].[klcv_nr] = fWin2KUserName() or
fWin2KUserName() = ""

Wayne

 

[Steff_DK]

These give the same result:

WHERE [tbl_chk_FA].[klcv_nr] = fWin2KUserName() or
[tbl_chk_FA].[klcv_nr] = ""

and

WHERE [tbl_chk_FA].[klcv_nr] = fWin2KUserName() or
fWin2KUserName() = ""



tbl_test_FA values are still left out, if there are no tbl_chk_fa entry for it...