Compile Error In Query Expression (1 Viewer)

mtagliaferri

Registered User.
Local time
Today, 13:15
Joined
Jul 16, 2006
Messages
519
Hi,
I had to reinstall Microsoft Office Package an I have now an issue with a DB I have been using for years, it is stuck on a Splash Form, I have removed the splash form by setting the DB to open on the main form which it use to open after the Splash Forms but I am getting the error as in attached screenshot.

Any ideas?
 

Attachments

  • Capture.PNG
    Capture.PNG
    4.8 KB · Views: 216

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe check if you have any "missing" references.
 

strive4peace

AWF VIP
Local time
Today, 08:15
Joined
Apr 3, 2020
Messages
1,003
mtagliaferri, actually, it looks like the error message is correct that something is indeed wrong with '[TAFB]*[DutyPayRate]+Nz(]TOTAdditionalAllowances],0'

perhaps NZ was added to account for error, but the syntax for Nz (null-to-zero) is:
Rich (BB code):
Nz( expression, value-if-null)

there should be a final parenthesis
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 08:15
Joined
Apr 3, 2020
Messages
1,003
@mtagliaferri, whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing any code.

from the menu in a VBE (module) window: Debug, Compile [the name of your project]
(Alt-F11 to switch to the code window)

Fix any errors on the highlighted lines.
Add needed references, remove unnecessary references, resolve missing references
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up by the compiler as well as many other errors than can be fixed before running.

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:
Rich (BB code):
 Option Explicit  ' require variable declaration
If this was not done when the code was written, you will may need to DIM some variables -- it is best to do that anyway
 

strive4peace

AWF VIP
Local time
Today, 08:15
Joined
Apr 3, 2020
Messages
1,003
@mtagliaferri, adding on again ... in this case, it appears this error is in a query expression not code (although VBA should also be compiled successfully and saved).

You can document the SQL for queries and test them with this free tool:

Tool > Add-in > Document SQL, RecordSource, RowSource for Queries, Forms, and Reports
 

mtagliaferri

Registered User.
Local time
Today, 13:15
Joined
Jul 16, 2006
Messages
519
From VBA under Tools->Reference I have the the available references as per screenshot.

Before I re-installed Office 365 the DB was working perfectly, so I believe is some settings or adds missing.
 

Attachments

  • Capture.PNG
    Capture.PNG
    19 KB · Views: 224

onur_can

Active member
Local time
Today, 06:15
Joined
Oct 4, 2015
Messages
180
I can suggest you a way like this. First, create an empty database, then transfer all the objects in your old database to your new database using table, form, report etc. import method. then run the Repair database command, it needs to be fixed.
 

Micron

AWF VIP
Local time
Today, 09:15
Joined
Oct 20, 2018
Messages
3,476
Rebuilding is sometimes done too quickly. Since this is a query issue, I would open whatever it is (form/report) in design view and examine the recordsource for it. This could also be a recordsource for a control, or a filter property or maybe something else, such as code. Info provided in the posts doesn't help us to help you narrow that down. Regardless, once you've found it, copy the sql. Open a new query (just dismiss the dialog to add tables), switch to sql view and paste in the sql. Try to switch to Datasheet view (don't run it unless it is a simple select query). If it balks, the query usually swaps back to sql view and highlights the offending portion.

Note - what you paste in must be raw sql. If it is concatenated in code, you will have to remove the concatenation. If you can do this and it highlights the problem but you cannot decipher the problem, post the entire sql here. You could also post a zipped db copy for inspection, perhaps.
 

Users who are viewing this thread

Top Bottom