"Invalid argument to function" - I'm perplexed

[Bob_Mackenzie]

Hi All.

I am tearing my hair out at this one. Access 2012

The query in the first pic runs fine, and will run fine with all the fields as long as I don't add 0nn or 0qun to the query

Once I add 0nn or 0qun to the query it get an ""Invalid argument to function" error.

The data in 0nn is the same as the data in 1nn, 2nn etc. I can't work out why its failing and how to fix it.

Help please!

Cheers Bob


This works...

Output from working query

This doesn't work

Input data

 

[Ranman256]

is [ASW UNION NEW...] a TABLE or QUERY?
a table should not throw any errors, but if its a query and you have a function then it could be there.

also
Access doesnt like you using Reserved Words as field names like: Type, Date, IS.

 

[Minty]

Try temporarily renaming those fields so they don't start with a 0 in the field name.
I seem to remember something weird like this once before. I know that table names can't begin with numbers in SQL server, fields are ok, though not very descriptive.

 

[Gasman]

I tried a DB of mine with same name 0nn and it was fine in a query?

 

[The_Doc_Man]

Though it might complicate what you are trying to do, the presence of numbered fields with otherwise the same names and (by your own admission) holding the same type of data means that you have a normalization error - which is something you CAN handle later, once you get past this error. Look up "database normalization" on the general web or "normalization" in this forum. In particular the error is "repeating groups" - which you have.

As to the error you see, you must find what function you are calling and then determine what the function wants. The error wasn't "type mismatch" so this has to be more along the lines of the way you are passing the argument than what the argument actually is. Beyond that, I can't advise since we can't see the code related to this situation.

 

[Deleted member 147267]

There's no mention or evidence of a function that I see so if there isn't a function call, the error message has nothing to do with a function. Looks like the addition of a table field causes it. Smells like corruption but it could be the awful naming convention being used here. Sorry Bob, but it is truly bad. Special characters in object names (Ship's) is bad enough, but starting an object name with a number is worse.

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

 

[Bob_Mackenzie]

Thanks all for your thoughts. I am a self taught amatuer which explains the dreadful naming conventions. I will try renaming the fields.

I can confirm that although the ASW union calls functions it runs perfectly well on its own without errors, I only get the error when its joined to the ships table

 

[Deleted member 147267]

My apologies to TDM for any irritation suffered as there was a clue in post 2.
Bad sleepless night, late to rise as a result and still no coffee - not a good combination.

 

[The_Doc_Man]

Aw, heck, Micron, you apologized just before I was going to blast you... (not! ). And I TRULY understand trying to operate without coffee. I was with the U.S. Navy for over 28 years. You just DON'T hang out with the sailors in the absence of coffee. That is a keel-hauling offense.

@Bob_Mackenzie - you are a novice and can be forgiven for stumbling over Access speed bumps. Here are a couple.

First big bump - having spaces in field or table names means you can NEVER EVER allow those names to appear outside of brackets. Remove the spaces so that those long names run together. (But you could shorten them to save typing even more.)

Second big bump - having reserved words in long names that might inadvertantely appear without brackets. Ranman256 pointed this out but it bears repeating. The word "UNION" appears in a table name and is surrounded by spaces. The word "NEW" appears in the same context. That's two reserved words right there. The word "DIR" appears in a field name with the "embedded spaces" problem.. This happens to be the name of a function. You have a field name of "TYPE" that will absolutely KILL you the first time you try to use that field qualified by its table name, as MyTable.Type, because it is a property name.

I mentioned normalization. It is a good topic for reading, but that problem isn't the source of the error message. All that will happen with bad normalization is that you will have to do things in an incredibly awkward manner because of poor data design.

It sounds like we are dumping on you, but we are not trying to do that. All we really want to do is assure that you realize that we can tell you better ways to do things. It is sort of a hazard that goes with asking questions of "experts" of our level of experience. You DO know the meaning of the word "experience" right? It means "able to recognize your mistakes when you make them again." We are telling you about certain design mistakes because we know them all too well.