Icon Visible if

[mtagliaferri]

When I corrected the code to

=IIf([SwapCheck]>0,DCount("[IDDuty]","tblSwaps","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#"),0)

the below comes up

 

[Gasman]

Upload the DB, else we will be here forever.
Just need enough to see the issue, with instructions of what control/form etc.

 

[MajP]

Yup I guess it does work, not that I am going to use brackets there...

You can always use brackets and at times you have to use brackets. Imagine your table name was Table One. Just like field names spaces, reserved words, or special characters require brackets around a table name in Sql, expressions, and code if using bang notation

 

[mtagliaferri]

You can always use brackets and at times you have to use brackets. Imagine your table name was Table One. Just like field names spaces, reserved words, or special characters require brackets around a table name in Sql, expressions, and code if using bang notation

@Gasman
I have attached a a version of the DB, with some data for the example

From the frmMain you can open a current duty or create a new one

In simple the IcoSwap is to highlight if any new duty created is a consequence of a swapped duty, the table swaps contain information of any date the duties have been exchanged in advance of the actual duty.

Therefore when a new duty is created if the date entered matches one from the table swaps the icon should be visible.

The comparison is via the the DCount function within the txtbox SwapCheck in the frmDuties

I solved the error by placing a Now() withing the default value of the dutydate and it actually works, but I would like to avoid this solution and have removed it to re-create the error

On a new record the DutyDate has an after Update event to make the icon visible.

I am sure you will find many long winging errors in my DB and better ways of achieving this any feedback is welcome

 

[Gasman]

Before you upload anything, you need to test that it works?

I get this when trying to open form main? That field does not exist in that table?

Why do you have this code for the Open Event of frmMain?

Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    stDocName = "frmMain"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Edit:
IDDuty does not exist in tblSwaps either?

=DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#")

 

[Gasman]

You also need Option Explicit at the top of every module, form, report etc.

You need to add it manually for all those created so far and tick the Require Variable Declaration for future code.

 

[mtagliaferri]

You also need Option Explicit at the top of every module, form, report etc.

You need to add it manually for all those created so far and tick the Require Variable Declaration for future code.

View attachment 117821

Hi @Gasman,
Apologies I modified the DB to the minimum.

I have a Maximise purely to make it fit to the screen of my device, I am aware changing device the formatting is all wrong.

With the code

=DCount("[IDDuty]","[tblSwaps]","[DutyDateNew] = #" & Format([DutyDate].[Value],"mm/dd/yyyy") & "#")

I am puzzled, I see your point that there is no IDDuty in the tblSwap, but the function seem to be working and is returning the count of records that date is equal in both tables??

With regards to Option Explicit can you give me more info as I create the code from the Choose Builder wizard, how would I add option explicit?

 

[Gasman]

With regards to Option Explicit can you give me more info as I create the code from the Choose Builder wizard, how would I add option explicit?

As I mentioned. Add it manually to what you have now, and set that checkbox for future objects. This way when you compile it will advise of mistyped/missing variables.
You could just as easy use an * for the field to retrieve I believe.?

? dcount("*","emails")
 1706