Problem with DCOUNT

[smaier]

Hello, all!



I'm having a problem with the DCOUNT function phrasing (or is it concatenation?) I believe. It's returning a zero value even when it looks like it should. The purpose is to check if the preventative maintenance for their machine has been done that day as they fill out their applied labor form.

From what I can tell the variables are holding the correct values so I'm thinking the problem is within the DCOUNT line.

Does anything stand out as being wrong?

Private Sub Machine_LostFocus()

Dim CheckPMForMachineVar As Variant
Dim MachineNumberVar As String
Dim MachineDBVar As String
Dim MachineVariable As Byte
Dim MachineFormVar As String


MachineVariable = Machine.Value


If MachineVariable = "1" Or MachineVariable = "2" Or MachineVariable = "3" Then
    MachineDBVar = "FemcoPMLogData"
    MachineFormVar = "FemcoPMForm"
ElseIf MachineVariable = "4" Or MachineVariable = "9" Then
    MachineDBVar = "HaasPMLogData"
    MachineFormVar = "HaasPMForm"
ElseIf MachineVariable = "8" Or MachineVariable = "10" Then
    MachineDBVar = "HardingePMLogData"
    MachineFormVar = "HardingePMForm"
ElseIf MachineVariable = "11" Then
    MachineDBVar = "DoosanPMLogData"
    MachineFormVar = "DoosanPMForm"
End If


CheckPMForMachineVar = DCount("PerformedOn", MachineDBVar, "[PerformedOn] = #" & RunDate.Value & "#" & " AND " & "[Machine] = " & Machine.Value)
'CheckPMForMachineVar = DCount("PerformedOn", MachineDBVar, "[PerformedOn] = #" & Format(RunDate.Value, "mm/dd/yyyy") & "#" & " AND " & "[Machine] = " & Machine.Value)


'MsgBox CheckPMForMachineVar


If CheckPMForMachineVar = 0 Then
    MsgBox "It looks like Preventative Maintenance has not been performed on this machine today." & vbNewLine & vbNewLine & "Please remember to perform machine PMs as soon as possible."
Else
    Exit Sub
End If


End Sub

[ranman256]

don't use txtBox.value. Just use txtBox. Ive had bad returns using .value.

you also have field MACHINE as a numeric value ,(correct?) If the field is a string, then the dlookup is: ....

"[Machine] = '" & me.Machine & "'"

[CJ_London]

plenty potentially wrong

rundate needs to be in the format mm/dd/yyyy or yyyy-mm-dd. You're OK if you use the US date format, but if UK then

1/11/2021 will be interpreted as 11th Jan, not 1st Nov

however you have commented out that code

You don't need .value anywhere here - it is the default and just means more typing and more chance of error

you are mixing data types

Dim MachineVariable As Byte - bytes are numbers but here you are treating them as text


If MachineVariable = "1" Or MachineVariable = "2" Or MachineVariable = "3" Then

And DCount will return 0 if nothing is found

[smaier]

Thank you very much for the replies!

I removed the *.value bits. Thanks for that, I'm surprised I haven't had problems with them before. It does simplify things. I also took out some of the things that aren't necessary (this was something I started on a few months ago and was having problems remembering where my head was at at the time).

I am wanting to keep the MachineVariable as Byte as that's how the data is defined in the table that the DCOUNT is pointing to. I also noticed at the end of the DCOUNT I was referencing Machine and not MachineVariable which I changed.

I'm still getting a zero return value on the DCOUNT, though. If the below code looks OK there must be a problem outside the DCOUNT.

Private Sub Machine_LostFocus()

Dim CheckPMForMachineVar As Variant
Dim MachineNumberVar As String
Dim MachineDBVar As String
Dim MachineVariable As Byte


MachineVariable = Machine


If MachineVariable = 1 Or MachineVariable = 2 Or MachineVariable = 3 Then
    MachineDBVar = "FemcoPMLogData"
ElseIf MachineVariable = 4 Or MachineVariable = 9 Then
    MachineDBVar = "HaasPMLogData"
ElseIf MachineVariable = 8 Or MachineVariable = 10 Then
    MachineDBVar = "HardingePMLogData"
ElseIf MachineVariable = 11 Then
    MachineDBVar = "DoosanPMLogData"
End If


CheckPMForMachineVar = DCount("PerformedOn", MachineDBVar, "[PerformedOn] = #" & RunDate & "#" & " AND " & "[Machine] = " & MachineVariable)


MsgBox MachineVariable
MsgBox MachineDBVar
MsgBox CheckPMForMachineVar


If CheckPMForMachineVar = 0 Then
    MsgBox "It looks like Preventative Maintenance has not been performed on this machine today." & vbNewLine & vbNewLine & "Please remember to perform machine PMs as soon as possible."
Else
    Exit Sub
End If


End Sub

[Welshgasman]

Put the criteria into a string variable and debug.print that, until you get it correct.
Then you can use that variable in your DCount()

You can also post the result of the debug.print back here, if you still cannot get it to work?

[Micron]

Edited because I just realized that the word I focused on is a variable and not a field name. I would have thought it's not possible to simply reference a variable in the function for the domain value. Perhaps if you concatenate. Can the domain variable value possibly contain spaces? Then you'd need brackets for sure [ ].

This part still seems applicable.
I suspect you have a design issue, unless I'm off base with what I think I'm seeing. If you add a machine to the mix (say it is numbered 15) will you have to edit this code? If yes IMO you have a tables design issue.

[CJ_London]

you still haven't addressed the point I made in post #3 about formatting dates

[davegri]

It probably is the date format. I got the code to work fine with the OP's VBA and US date.



Here's the DB:

smaier-davegri-v01.zip

[Micron]

I see that it is. Had to walk the mutt first - should have waited and tested.

[smaier]

Thank you all so much for the help thus far, all!


Here's the output of the debug.print:

If the criteria is "[PerformedOn] = #" & RunDate & "#" & " AND " & "[Machine] = " & MachineVariable I get:
[PerformedOn] = #11/1/2021# AND [Machine] = 1

If the criteria is "[PerformedOn] = #" & Format(RunDate, "mm/dd/yyyy") & "#" & " AND " & "[Machine] = " & MachineVariable I get:
[PerformedOn] = #11/01/2021# AND [Machine] = 1

For what it's worth I'm using US dates, and in the table it's stored as a short date and reads 11/1/2021



Edit: If the above dubug.print outputs seem OK I may just copy the controls for this one problem and put them into a completely separate database to share. Right now the db is way too large to post and I know it can be difficult to pin down a problem without being able to see the other details that may not be mentioned.

Edit#2: Fixed which output came from which criteria.

[Welshgasman]

I would say the first is fine?
I would say for the second, I cannot see how that would equate to 11/1/2021 when the format string is mm/dd/yyyy ?

[smaier]

Ack! My apologies, I did them both in sequence and got the criteria switched. Edited my post above.

In the morning I'll try and pare it down to a db small enough to post.

Thank you so much for your help and patience, all! I have this sneaking suspicion it will be something super simple that I haven't shared or has escaped my mind.

[Welshgasman]

You could also show the table properties for RunDate and Machine?

[CJ_London]

and perhaps a snippet of the data to show that there are records with the appropriate date and machine number

[smaier]

OK, I have stripped out most everything that isn't relevant to make the file size small enough. I've spent a good portion of the evening trying to figure it out but I'm not getting anywhere. The code is where I left it after poking it with a stick. If the past has taught me anything it will wind up being something very simple that I'm not thinking of questioning.

I hate going to bed a beaten man .