Syntax error in expression

[Perfac]

On our "Salary processing" form, which work well for a long time, has a button "Cmd017" when clicked on it creates a Salary Journal. I am trying to get an expression on the form f03SalaryProcesssing to make visbility false, if there is already a Salary Journal in the month in the query q02Journal. On the form the Salarydate is named EDate which will be the date in the q02Journal if there was already a Salary Journal Posted. Currently, duplication is possible.

If Not IsNull(DLookup("JournlID008", "q02Journal","TransactionType008a=" & """Salary Journal""" and #" & DocumentDate008a=#" & Format(Me.EDate, "dd-mm-yyyy") & "#")) Then
Me.Cmd017.Visible.False
Else
Me.Cmd017.Visible.True


End If
End Sub

[Micron]

I suggest you do away with the nested double quoting and use singles instead. Easier to follow. Your " and " looks like it is out of place. Same with your =#"
Also, if this is vba, IsNull is for sql. Is Null is for vba.

EDIT - and it's = False, not .False
You also have # date delimiters on the wrong side of the =.

[June7]

Your delimiter characters and concatenation are messed up. Can use apostrophe as embedded text delimiter. Consider:

If Not IsNull(DLookup("JournlID008", "q02Journal","TransactionType008a='Salary Journal' AND DocumentDate008a=#" & Format(Me.EDate, "dd-mm-yyyy") & "#")) Then

If you use international date structure, review http://allenbrowne.com/ser-36.html for example of formatting date so Access will properly handle.

@Micron, I think you have it backwards. IsNull() is VBA function. Is Null is used in SQL.

[Micron]

@Micron, I think you have it backwards. IsNull() is VBA function. Is Null is used in SQL.

Crikey! How many times have I said it the right way? I shouldn't post when I'm hungry. Speaking of eating, sometimes I let the OP give it a try before spoon feeding the answer. Helps to build stronger coding skills I think.

[Perfac]

Following is code that works well for us, my son Ruben set it up. It was a little different and the criteria has one condition. In this thread there are two conditions. Don't bother what comes after "THEN", I will fix that. But please take my expression at the top, change and repost. Thank you.

'If Not IsNull(DLookup("SalSlpID067", "q03SalaryRecords", "Salarydate067=#" & Format(Me.EDate, "dd-mm-yyyy") & "#")) Then
'Me.Cmd017.Enabled = False
'Me.Cmd019.Enabled = True
'Else
'Me.Cmd017.Enabled = True
'Me.Cmd019.Enabled = False
'End If

[orange]

You might want to consider IsDate().

[Perfac]

Solved.
I created a query which filters q02Journal, by the word Salary Journal in one field and by the date in another. If this new query q04Journal02 has any records it means a Salary Journal for the month has been done, and the "Create Journal" button visibility will be false. I used iif IsNull. Didn't need any criteria. I keep a long list of examples of expressions, since I am just not getting to a point to always remember how expressions should be exactly. So in my opening thread there is the suggestion. If someone sees my exact mistake I would still like to put this expression on my list where it works.

This works now.
If IsNull(DLookup("JournlID008", "q04Journal02")) Then
Me.Cmd017.Visible = True
Else
Me.Cmd017.Visible = False
End If

[CJ_London]

I would be surprised if this works as expected - except by accident. i.e. eDate is always greater than the 12th of the month, which assuming edate is the end of the month (when salaries are usually paid) it would be normally

"Salarydate067=#" & Format(Me.EDate, "dd-mm-yyyy") & "#"

But if salaries are paid 4 weekly, you will experience problems The interpreter is bright enough to know there are only 12 months in the year so will interpret 31-12-2020 correctly as 31st December. However 11th June (11-06-2020) will be interpreted as 6th November.

[Perfac]

Thanks. I articled as an auditor too many years ago, but became a businessman after that for more than 30 years. Never heard of, or experienced a business paying salaries other than once a month. Our system is set up that any "SalaryDate" can be selected, but we convert it to create a field that forces it to the last day of any month. Yes Edate is the last day of any month selected. We have a month table t01Month. My lifelong experience in SA is that 4 weekly remuneration is called "Wages", our system manage Salaries and Wages separately. Thank you. I think Ajax may be the most clever advisor on here?

My system is called PERFAC, and we have a "BusinessPolicy" form where a user can select between three different policies a Business may when a business has a Pension Fund or Medical Aid for their employees, and maybe a different policy on Bonus or leave. A user make a selection of one of those three options then PERFAC calculates according to it. I think it covers all possibilities. If a business do not have a Pension fund, the word Pension Fund will not be found in PERFAC. If I was born 10 years later I would have been a developer. The ship with the knowledge took too long from the first world to the third world, says a "dutchman" born in a spot worse than Holland.

[Perfac]

Ajax. Edate is on my wages form. Use Salarydate on the SalaryProcessing form. You suggested Salarydate067 ?. The date Salarydate should be compared to, is Documentdate008a, both would be a last day of a month irrespective what day the user captured. My latest error is "Type Mismatch" on the expression as below.

If Not IsNull(DLookup("JournlID008", "q02Journal", "TransactionType008a=" & """Salary Journal""" And "Documentdate008a=#" & Format(Me.SalaryDate, "dd-mm-yyyy") & "#")) Then

The solution I found works well, but I would like to have this example on my list.

[CJ_London]

In the UK salaries are defined as a fixed payment for a period of time regardless of the hours actually worked - might be a day/week/fortnight/4 weekly/monthly whilst wages have an hourly rate applied to the hours actually worked. So a different basis of calculation. Both can have additional payments such as overtime or bonus/commission. Wages by definition have to be paid after the period has been worked whilst salaries can be paid during the period - e.g. monthly salary paid on the last Friday of the month. My clients are mainly national/international companies many with complex payroll requirements and many with multiple salary and wage profiles.

[CJ_London]

You suggested Salarydate067 ?.

no - I was quoting your code in post#5

[Perfac]

Post#5 is a copy of an expression in our VBA on the Wages page that works exactly like that. No matter.

What you brought to my mind, is our system must be able to handle different pay profiles inside one company. I am involved with a company CO Space. They rent out small workshops, and office space to as many as 700 small businesses. They do administration and other services for less than 10% of them, but we plan to optimize that. Small businesses will probably not have different remuneration profiles, but our system must be able to do that. As a business owner I thought that Pastel, Xero Quickbooks and other only do a fraction of what I want a system to do. Since I came into developing the system I always wanted, I like to express myself saying a system must give a business 100% of the potential of what IT is there for. I think most businesses are not 20% there yet. Supermarkets around me still sell "Statement books" for businesses doing their statements manually!

[Micron]

If someone sees my exact mistake I would still like to put this expression on my list where it works.

I thought you got the answer to that in post 3.
BTW, when I was on salary, I got paid bi-weekly. The company had both hourly and salaried employees. When you think about it, it makes sense to put everyone on the same frequency otherwise you're running 2 systems.

[Perfac]

Sorry Micron I was fast asleep. I looked over it and at short sight thought you just quoted me. I will test it and if it works, great!

OK I tested it, it works well. I am very grateful!