Displayed Text in Textbox based on Query Access 2003 1

[pccarrick]

Hi wonder if somebody could help out on this problem i am having. I have several different columns that have yes/no values assigned to them. What i am wanting to do is if any records in one of the columns returns the value as yes then i want to change a text box to display "Data Found" or if nothing is returned display "Data Not Found". I don't have to use a textbox but i thought this would be the right way to do this. I am doing seperate queries for each column, what i am trying to get out of this is a display that will tell me if the Criteria is meet without having to go further into the information.

Thanks for your help in advance.

 

[MajP]

SELECT
tblOne.otherFld,
tblOne.otherFld2
tblOne.yesNoFld1,
tblOne.yesNoFld2,
tblOne.yesNoFld3,
IIf([yesNoFld1]+[yesNoFld2]+[yesNoFld3]=0,"Data Not Found","Data Found") AS DataFound
FROM
tblOne;

Above shows both data found and data not found with labels.

or just the "data found' add the where clause
WHERE IIf([yesNoFld1]+[yesNoFld2]+[yesNoFld3]=0,"Data Not Found","Data Found")="Data Found

 

[pccarrick]

Hi thanks for the response do i add this as an event for the texbox or do i add it as part of the control source.

Thanks in advance

 

[MajP]

I find it must easier to make calculated fields within the query, instead of making a calculated control. So I add it to my query. Then the recordsource of the control is simply my calculated field name "DataFound"

The only drawback in doing it this way is efficiency. If you had 100,000 records then all 100k would have to calculate the value. If you put this as a calculated textbox and it is single form view it calculates only when looking at the record.

Can you post your query? I will show the function way for the textbox on the form.

 

[MajP]

Public Function isdataFound(ParamArray yesNoFlds() As Variant) As String
  Dim itm As Variant
  isdataFound = "Data Not Found"
  For Each itm In yesNoFlds
    If Nz(itm) = -1 Then
      isdataFound = "Data Found"
    End If
  Next itm
End Function

In a query

SELECT 
 tblOne.otherFld, 
 tblOne.yesNoFld1, 
 tblOne.yesNoFld2, 
 tblOne.yesNoFld3, 
 isDataFound([yesNoFld1],[yesNoFld2],[yesNoFld3]) AS DataFound
FROM tblOne;

or in a textbox on the form:
=isDataFound([txtBxOne],[txtBxTwo],[txtBxThree])

the function gives a lot of flexibility because you can enter as many or few yes no fields as possible

Such as
SELECT
isDataFound([yesNoFld1],[yesNoFld2],[yesNoFld3]) AS DataFound,
isDataFound([yesNoFld1],[yesNoFld3]) AS DataIn1And3Only
FROM
tblOne;

 

[pccarrick]

What i have in my table is like below. So what i want to do is for example run a query that checks HVAC, Haulage, Hydraulics and if any of the entries in the table have a value of YES then i want to display that this is "Valid" in a text box even though the other two have this as No. Or check if none of the entries have yes then display in the textbox "NotValid" . I understand that i might have to create a query for each column(s) i want to keep together but this is not a problem

Problem is i am new to Access and think i am digging in the wrong direction. So any queries or code would help me out a lot.

Thanks

Peter

Table Name - Suppliers

Haulage HVAC Hydraulics
No No No
No Yes No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No Yes No
No Yes No
No Yes No
No Yes No
No Yes No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No No No
No Yes Yes

 

[MajP]

Here is the function way. I redid the function to meet your desired output

1) Create a new vb module and drop this in

Public Function isdataValid(ParamArray yesNoFlds() As Variant) As String
  Dim itm As Variant
  isdataValid = "NotValid"
  For Each itm In yesNoFlds
    If Nz(itm) = -1 Then
      isdataValid = "Valid"
    End If
  Next itm
End Function

2) Open the query designer and choose the fields you want to show.
3) In the query designer to add a calcualted field first put the desired field name then your function. It would look like:
DataValid: isdatavalid([Haulage],[HVAC],[Hydraulics])

4) Or if you went directly to the SQL window you would write
SELECT
Haulage,
HVAC,
Hydraulics,
isdatavalid([Haulage],[HVAC],[Hydraulics]) AS DataValid
FROM
tblSuppliers;

5) My output looks like
Haulage HVAC Hydraulics DataValid
Yes Yes No Valid
No Yes Yes Valid
No No No NotValid
Yes No Yes Valid
No No No NotValid
Yes No No Valid
No No No NotValid
Yes No No Valid
No No Yes Valid
No Yes No Valid
No Yes No Valid
No Yes No Valid

)The pure sql solution without using a function would be:
SELECT
Haulage,
HVAC,
Hydraulics,
iif([Haulage]+[HVAC]+[Hydraulics]=0,"NotValid","Valid") AS DataValid
FROM
tblSuppliers;

 

[pccarrick]

Hi thanks again for your help, i have now got it returning the the results correctly for the different columns by using the the sql statment in my query but i have one last question. How do i add the query to the form so i can get the information to my text box ?

 

[MajP]

Use the query (or some version of the query) as the recordsource of the form. You probably are directly using the table, so just type the name of the query into the recordsource property instead of using the table name.

 

[pccarrick]

thanks again, if i add it as a subform on the form can i link the two so if i go from record to record they change
e.g if form one has name Peter and form two has apples. then when i go to next record they both change like form one Bob and form two pears ?

Thanks

 

[MajP]

Yes, no problem if you have designed your tables correctly. But It would help if you used real field names and table names and possibly some sample output. I am not sure exactly what you would like to link and to show.

 

[pccarrick]

Top Top Man..... i have now changed it to use a query as the record source rather than the table and it is all working spot on.

Thanks so much for your help ********* Man

 

[SkipVought]

pccarrick,

I notice that over the past 6 months, you have posted several times and have received many good tips related to your stated needs. Yet, you have responded NOT ONCE, to
[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,

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