05-02-2016 10:48 AM - edited 05-02-2016 10:50 AM
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"
Solved! Go to Solution.
05-02-2016 02:03 PM
Can you share the database file and down convert the VI to 2012?
05-02-2016 02:47 PM
OK, here it is...
05-02-2016 04:26 PM
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.
05-02-2016 05:37 PM
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.
05-03-2016 09:36 AM
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.
05-03-2016 10:15 AM
I don't get an array of values back - I still get the same error (see earlier post - error.png).
05-03-2016 10:34 AM
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.
05-03-2016 10:45 AM
Actually, it looks like Description is a reserve word. Try changing that field name to something else.
05-03-2016 11:30 AM
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.