Re: [MS_AccessPros] call a function from my query

 

Nope, that won't work.

Sarah-  What you're trying to do is generate a field name on the fly, but you can't do that in a query.

You might be able to do it with a custom function:

Public Function DayIsTrue(Mon As String, Tue As String, Wed As String, Thu As String, Fri As String, Sat As String, Sun As String) As Integer

Dim strDayName As String

    strDayName = DW1(Forms!zform1amb![pid from])

    Select Case strDayName

        Case "Mon"

            DayIsTrue = Mon

        Case "Tue"

            DayIsTrue = Tue

        Case "Wed"

            DayIsTrue = Wed

        Case "Thu"

            DayIsTrue = Thu

        Case "Fri"

            DayIsTrue = Fri

        Case "Sat"

            DayIsTrue = Sat

        Case "Sun"

            DayIsTrue = Sun

    End Select

End Function

SELECT dr1.*
FROM dr1
WHERE DayIsTrue(dr1.Mon, dr1.Tue, dr1.Wed, dr1.Thu, dr1.Fri, dr1.Sat, dr1.Sun)=True
ORDER BY dr1.[name];

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

SQL Queries for Mere Mortals 

http://www.viescas.com/ 

(Paris, France)

On Dec 3, 2014, at 5:04 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Remove the "dr1." from in front of the function name.  I would probably create a hidden text box on the form and set its control source to =dw1([pid from]).  then use the new text box in the query criteria.
 
Duane Hookom MVP
MS Access
 

---

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 2 Dec 2014 19:37:33 -0800
Subject: [MS_AccessPros] call a function from my query



I need help with the following:
Table=DR1. The table has check boxes for every day of the week : sun, mon, tue, etc
Function=DW1.The function  accepts a date and returns the day of the week for that day eg 12/2/2014=tue


in my query I am trying to test if dr1.tue is true, so I am calling dw1 function with a date ([pid from] contains the date) as follows:
SELECT dr1.*
FROM dr1
WHERE dr1.dw1(Forms![zform1amb]![pid from])=True
ORDER BY dr1.[name];


this results in error message : undefined function dr1.dw1.


if I add '"&, I can run it in VBA, but I would like to run it as a query,


How can I get the DW1 function to work in the query?.


Sarah

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___