calling module (Function) in database toolkit

[hasse]

Hi, I have been using the database toolkit a lot but I have never used a function from my Access database before.

I have a function called "Work_Days" that takes two dates as Inparameters and calculate how many working days that is. This funtion is stored in the Modules section in Access.

I have a working query that I can run within Access that looks like this:

SELECT tblActiveCCU.ID, tblActiveCCU.DateReceived, tblActiveCCU.DateTransferred, Work_Days([DateReceived],[DateTransferred]) AS NetDays
FROM tblActiveCCU
WHERE (((tblActiveCCU.ID)=97));

The output looks something like this:

ID	DateReceived	DateTransferred	NetDays
97	3/16/2016	3/30/2016	11

Now to the question, how do I implement this query in LabView?

In my attached code I got a "Syntax error in FROM clause"

[aputman]

Can you share the database file and down convert the VI to 2012? 

[hasse]

OK, here it is...

[aputman]

Rather than create a duplicate query in Labview, just call the query within the database from Labview.  It works like a stored procedure in SQL.  See attached.  

I added a parameter which can be referenced in the database/query to allow Labview to choose the ID.  In design view for the query, there should be a parameters button where you can add a prompt for value.  Once you have this created, the criteria for the ID field will be "=[param_name]".

I am not sure about the function call.  It doesn't work for me either.  Says that it's an undefined function.  A quick search says that this might be fixed in Visual Basic runtime 6.0.  

[hasse]

Aputman,

sending the query name seems to be a good solution, however I can't get it to work.I have an error  regarding the SQL statement.

[aputman]

Using the attached files, you should get an array of values back with an ID of what you type in the control.  You'll need to make sure the connection information is setup properly because it is coded for my PC at the moment.  

I had to delete the column with the custom function Work_Days from the query because of what I mentioned previously.  If you update to VB runtime 6.0, that might fix this issue.

[hasse]

I don't get an array of values back - I still get the same error (see earlier post - error.png).

[aputman]

I'm not sure what to tell you.  I found this reference to the error message but I don't see anything in your example that would cause this.  It works on my machine.

http://digital.ni.com/public.nsf/allkb/22A45DF5C85AB3F286256CC30073C5AC

Try removing the insert data statement and see if you can query data back.  

[aputman]

Actually, it looks like Description is a reserve word.  Try changing that field name to something else.

https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9...

[hasse]

I changed the reserved word (Description) - I still get the error, I have used this in the previous code I am using so maybe it is OK because I have Access 2010.

I can read back the parameters out (test, Long (I32), Input, 3). The error comes in the EXE VI.

If I put a value in NetDays this values is still written into the database in the NetDays column though.

So you haven't changed anything in the column string array?

What do you mean by: "Try removing the insert data statement and see if you can query data back."

I removed the whole Insert data VI and the surrounding strings, the error is still the same.