Accessing a query from VBA

[SeadnaS]

I'm not sure if this is the right place for this question. I have a query that shows if the word fail is in a list of certain records:


SELECT CHILD.MODEL_NO, CHILD.TEST_TYPE, CHILD.LOT_NO, CHILD.PASS_FAIL AS fail
FROM CHILD
GROUP BY CHILD.MODEL_NO, CHILD.TEST_TYPE, CHILD.LOT_NO, CHILD.PASS_FAIL
HAVING (((CHILD.MODEL_NO) Like [forms]![NEW_MASTER_FORM]![MODEL_NO]) AND ((CHILD.TEST_TYPE) Like "PROX_TENSILE") AND ((CHILD.LOT_NO) Like [forms]![NEW_MASTER_FORM]![LOT_NO]) AND ((CHILD.PASS_FAIL) Like "fail"));

This shows the word FAIL in the fail column if fail is in the selected records if otherwise the query runs blank.

I want a text box on my form that runs that query and shows FAIL if the query shows FAIL and PASS if the query doesn't. I figure i need to do this in VBA. There's probably a much easier way to do this than what I'm trying but I can't quite figure it out.

 

[SkipVought]

hi,

The Like operator usually has a format like this...

 [Your Field] Like '*Somevalue*'

where the ASTERISK is a wildcard indicator and the value delimited with APOSTROPHYS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SeadnaS]

Yes I realise that. I just used it lazily as i wasn't sure whether the text was going to be upper case or lower case.

 

[SkipVought]

Then you might want to use...

 ucase([Your Field]) Like '*SOMEVALUE*'

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SeadnaS]

Thanks but that's not quite what I'm asking for.

 

[dhookom]

that's not quite what I'm asking for

Maybe you should try explain again. Apparently both Skip and I don't understand your question.

I think you might need an expression using DCount() or something but it's impossible to tell based on your postings.

Duane
Hook'D on Access
MS Access MVP

 

[SeadnaS]

Ok sorry for the bad explanation. I have solved the problem. I scrapped that useless query. (As I have said before I have no formal training in databases so sorry for not making any sense.)

I already had a query created that I added a column to and i got the average of that column and compared my numbers to the average.

Simple "if" statement:

If Me.sigma.Column(1, 0) >= 5 Then
p6.Value = "PASS"
Else: p6.Value = "FAIL"
End If

Voila.

 

[SeadnaS]

sigma is a hidden listbox btw.