Compile Error In Query Expression

[mtagliaferri]

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?

 

[theDBguy]

Hi. Maybe check if you have any "missing" references.

 

[strive4peace]

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:

Nz( expression, value-if-null)

there should be a final parenthesis

 

[strive4peace]

@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:

 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]

@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

Document SQL

Document SQL statements in Queries, Forms, and Reports, and find errors so you can fix them

msaccessgurus.com

 

[mtagliaferri]

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.

 

[onur_can]

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.

 

[Deleted member 147267]

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.