Using function in query

[deejay_totoro]

Hello,

I am trying to pass the value of a text box to a query using VBA. What I have written is not working. Does anyone have any ideas?


Public Function CriteriaToApply()

' First get value in text box. This will be the criteria applied to the query
CriteriaToApply = ([Forms]![frmMain]![MyField])

If (CriteriaToApply) = "All" Then
CriteriaToApply = ' <= Something here to return all records

Else
CriteriaToApply = CriteriaToApply ' <= Will return records that match the text box

End If

End Function



Im the criteria of a field in my query I have placed: =CriteriaToApply()

Any ideas?

Thanks very much!

dj_T

 

[jal]

What code are you using to execute the query after the data is placed in the textbox? Have you placed a breakpoint in that code to see if it is executing?

Have you placed a breakpoint in the Function?

Why is your IF-Then incomplete?

If (CriteriaToApply) = "All" Then
CriteriaToApply =

 

[deejay_totoro]

Hello,

Its not that it's incomplete:

If (CriteriaToApply) = "All" Then
CriteriaToApply = ' <= Something here to return all records

I just dont know what to put, so thats why I left the comment in.
' <= Something here to return all records

What is required is something that will return everything in the query.

Cheers!

dj_T

 

[rainman89]

have you put
[Forms].[frmMain]![MyField]
in the criteria for the query in your query wizard?

you can also use a
LIKE *
that should return everything

 

[WayneRyan]

dj_T,

Ray has your answer ... here's the Criteria:

Like "*" & [Forms].[frmMain]![MyField] & "*"

Wayne

 

[deejay_totoro]

Cheers!

I suppose I am trying to do it all in VBA? So is there a way to add something similiar to:

Like "*" & [Forms].[frmMain]![MyField] & "*"

to my original VBA? For example:

If (CriteriaToApply) = "All" Then
CriteriaToApply = Like "*" & [Forms].[frmMain]![MyField] & "*"

Thanks again!

 

[rainman89]

you are trying to run a query based on what is typed into the textbox correct?

are you trying to return this to a form? or what are you trying to do with it?

 

[deejay_totoro]

Hello,

I would like to take the value from the text box and send it through some VBA which will then become the criteria for a query.

So in my query the criteria would call my function: CriteriaToApply(), instead entering a condition in the query condition.

Thanks again!

dj_T

 

[rainman89]

is there only certain criteria that will be used? like "All" or something else along those lines? because I believe you can do this without creating a function to do it

 

[WayneRyan]

dj,

Criteria:

Like "*" & CriteriaToApply([MyField]) & "*"

But ... you can do away with the function and just use the reference
to the form.

btw,

Your function HAS to be in a Public Module.
It HAS to return a value.
It should NOT have the same name as the module it is in.

hth,
Wayne