HOW CAN I KNOW NULL VALUES...? 1

[aqm]

HI,
HOW CAN I CHECK NULL VALUES FROM THE TABLE BY QUERY SQL?
IS THERE ANY TIP?
HELP
AQM

 

[pgk]

Hi,
You could use the Is Null keywords to check if a particular field is null.

Something like "Select * from Table where Field1 Is Null"

You can use the Is Not Null to check if a field is not null. Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

HI, THANKS. I WANT TO CHECK EVERY RECORD ON THE TABLE WHETHER THERE IS A NULL VALUE OR NOT. DOES THAT HELP?
AQM

 

[pgk]

Hi,
The sample query that I posted in my previous message will retrieve only records from Tabel whose Field1 is null.

Another example:

"Select * from employeeRecords where retirementDate Is Not Null"

Retrieves only employee recors where the retirmentDate contains a value. Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

hi,
thanks. now how can i know the result of an sql query whether the value is there or not. i want it to inform me if it does not find the value.
i hope i made myself clear on this.
aqm

 

[pgk]

Hi,
I will assume you are using DAO to connect to your database. Set a refernce to the Microsoft DAO 3.6 Object Library and use the following code:

Option Explicit
Dim db as DAO.Database
Dim rs as DAO.Recordset

Private Sub GetNumberOfRecords_CLick()

Set db=CurrentDB
Set rs=db.OpenRecordset("Select * from employees where retiredDate Is Not Null",dbOpenDynaset)

If rs.RecordCOunt > 0 then
rs.MoveLast
rs.MoveFirst
Msgbox "There are " & rs.RecordCount " retired employees"

else

Msgbox "There are no retired employees"

End If

db.CLose
rs.CLose
Set db=Nothing
Set rs=Nothing

End SUb Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

hi,
thank you PGK. that´s extremely excellent. but iam not using DAO. Iam using ACCESS resources. is it possible with access only?
other question: is it possible to make information display on various forms at one query or select?
aqm

 

[pgk]

Hi,
What exactly are you using? I thought you were programming with Access. Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

hi,
yes. iam programming with Access. this is the code iam using:

stDocName = "Filtro"
operBusca = "AND"
qrbusca2 = "[DADO_PESSOAL].[NU_CONTRIBUINTE]=" & "'" & Me![NU_CONTRIBUINTE_FR] & "'"
qrbusca3 = "[DADO_PESSOAL].[FUN_CIF]=" & "'9999999'"
qrbusca = qrbusca2 & " " & operBusca & " " & qrbusca3
DoCmd.Close acForm, "FRR_Busca"
DoCmd.OpenForm stDocName, , , qrbusca

How can I know if the return value was not found?
this is my problem.
aqm

 

[pgk]

Hi,
If you can use DAO, then make the following modifications in the code I posted earlier.

Set db=CurrentDB
Set rs=db.OpenRecordset("Select * from DADO_PESSOAL where [DADO_PESSOAL].[NU_CONTRIBUINTE]='" & Me![NU_CONTRIBUINTE_FR] & "' and [DADO_PESSOAL].[FUN_CIF]= '9999999'"


If rs.RecordCOunt > 0 then
DoCmd.Close acForm,"FRR_Busca"
DoCmd.OpenForm "Filtro"
else

Msgbox "There are no records matching your criteria"
DoCmd.OpenForm "FRR_Busca"

End If

db.CLose
rs.CLose
Set db=Nothing
Set rs=Nothing

End SUb


You can set a refernce to the Microsoft DAO 3.6 Object Library using Tools->References in the Visual Basic Code Editor.

Note: In the above code, if the Field [DADO_PESSOAL].[FUN_CIF] is numeric, delete the single quotes around the 9999999. Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

hi,
this was excellent. please bear with me.
scenary:

I want to make a query that will display information of numerous employees on different forms that are bound together by tabs. if i select one employee, his information must be displayed on the various forms.

how about menu in access?
can u help?

 

[pgk]

Hi,
If you are using DAO and retrieve a particular record, you can assign the data to various fields before you close the recordset.

Something like

[Forms]![Form2]![txtName]=rs.Fields("name&quot
[Forms]![Form3]![txtName]=rs.Fields("name&quot

Regarding menus, Right click on the menu bar at the top->Customize->Tool Bars->New. Enter the name of the tool bar.

To place buttons on the toolbar, click on the Commands tab and drag and drop the command that you want on the newly created tool bar. All these dropped commands will retain their original functionality. Like new will open a new form.

To create your own functionality for buttons, Drag and Drop the Custom on your toolbar. Right this button on your toobar to set its properties. Hope it helps. Let me know what happens.
With regards,
PGK

 

[aqm]

HI,~
THANKS FOR YOUR ASSISTANCE.
HOW CAN I SYNCRONIZE THE A FORMS WITH A SUBFORM? BECAUSE WHEN I OPEN THE FORM AFTER HAVING SYNCRONIZED THE COMBO BOXES THROUGH THE FILTER ON THE QUERY, IT GIVES ME AN ERROR SAYING THAT I DID NOT USE A FILTER.
CAN U HELP? IS THERE ANY CODE FOR A FILTER, INSTEAD OF DOING IT THROUGH THE QUERY?
AQM

 

[aqm]

HI,
HOW CAN I SYNCRONIZE THE FORM WITH THE SUBFORMS?
AQM

 

[pgk]

Hi,
I don't know anything about sub forms since I have never used them. So I suggest that you post this as a separate question in the forum. Hope it helps. Let me know what happens.
With regards,
PGK