DBA Code - Identify if 2 fields within a table have been populated

[brucey54]

Hi folks, need some help with this code, basically trying to identify if 2 fields within a table have been populated i.e.

Check date field and restaurant field, if these fields have been populated do

this

errStr = errStr & tmpDate & ", "

Else

validStr = validStr & tmpDate & ", "
stDocName = "McrReOrderDietPlanCRX"
DoCmd.RunMacro stDocName

End If

Code below, the If DCount("*" is comming up with a error, thanks in advance

####################################################################################

Private Sub Command37_Click()

Dim startDate As Date, endDate As Date, tmpDate As Date
Dim loopCtr As Long, dayCtr As Long, errStr As String, validStr As String

startDate = InputBox("Enter the Start Date")
endDate = InputBox("Enter the End Date")
dayCtr = DateDiff("d", startDate, endDate)

For loopCtr = 0 To dayCtr

tmpDate = DateAdd("d", loopCtr, startDate)

If DCount("*", "TblReOrderDate", "[ReOrderDate] = " & Format(tmpDate, "\#mm\/dd\/yyyy\#") <> 0 and "TblRestaurant", "[Restaurant] = "Watersidel") Then
errStr = errStr & tmpDate & ", "
Else
validStr = validStr & tmpDate & ", "
stDocName = "McrReOrderDietPlanCRX"
DoCmd.RunMacro stDocName
End If

Next

If Len(errStr & vbNullString) <> 0 Then
errStr = "Date(s) : " & Left(errStr, Len(errStr) - 2) & " already exists."
End If

If Len(validStr & vbNullString) <> 0 Then
validStr = "Date(s) : " & Left(validStr, Len(validStr) - 2) & " have been added."
End If

MsgBox errStr & validStr, vbInformation
End Sub

 

[TJPoorman]

You've got a major syntax error on that DCount. Try this:

If DCount("*", "TblReOrderDate", "[ReOrderDate] = " & Format(tmpDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = "Watersidel") <> 0 Then

 

[brucey54]

Hi TJPoorman, still not working,

I have 2 table tblReOrderDate with the field [ReOrderDate] and another table tblRestaurant with the field [Restaurant]

If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = " “Waterside") <> 0 Then

Any ideas?

 

[TJPoorman]

how are the two related?

 

[brucey54]

Hi, it’s a one too many relationship i.e.

The tblRestaurant can have many tblDietPlans

The tblDietplans can only have one tblRestaurant

 

[brucey54]

The error seems to be focus round TblRestaurant

Error message - Expected: list separator or )


If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND "TblRestaurant ", "[ Restaurant] = "Waterside") <> 0 Then

 

[DavidAtWork]

I don't think you can use DCount like this, the first part looks ok
If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#")) <> 0

but you can't now refer directly to TblRestaurant, you can only refer to a field in TblDietPlan, if TblDietPlan has a restaurant id, then you could try creating a query that joins TblDietPlan and TblRestaurant and now you can refer to both values

If DCount("*", "qryDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = " “Waterside") <> 0 Then ...

David

 

[brucey54]

Thanks David, I will give it a go.

Brucey54

 

[brucey54]

Hi David, still having the same problem, same error message and it keeps highlighting the field Restaurant, any ideas?

Error message - Expected: list separator or )

If DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = " “Waterside") <> 0 Then

 

[DavidAtWork]

can you post a list of the relevant fields in the relevant tables

David

 

[brucey54]

tblDietPlan
• DietPlanID
• RestaurantFK
• MealDate
• MorningDiet
• AfternoonDiet
• EveningDiet

tblRestaurant
• RestaurantID
• Restaurant
• Location
• Forename
• Surname

Query
• MealDate
• Restaurant

 

[DavidAtWork]

ok, last question, what is txtCusDate, if this is a text box on a running form then try modifying your query and use the query to set the criteria.
So in your query write "Waterside" in the criteria line under the Restaurant field and in the citeria line for MealDate field use the wizard to point it to your txtCusDate text box, then all you need in your DCount expression is:
If DCount("*", "QryPrintDataVBA") <> 0 Then ...

This assumes that txtCusDate is formatted as a date data type

David

 

[brucey54]

Hi David, will try your suggestion, the txtCusDate is indeed a textbox on a form.

thanks

 

[brucey54]

it work ok if I use the following code;

If DCount("*", "TblDietPlan", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#")) <> 0 The

 

[TJPoorman]

The problem is with your quotes around Waterside. Try like this:

If DCount("*", "QryPrintDataVBA", "[MealDate] = " & Format(txtCusDate, "\#mm\/dd\/yyyy\#") & " AND [Restaurant] = 'Waterside'") <> 0 Then

 

[brucey54]

Hi TJpoorman, thank you very much it worked

 

[brucey54]

Thank you TJpoorman, it work