Mr Banan Qry
New member
- Local time
- Today, 07:49
- Joined
- Jul 3, 2012
- Messages
- 9
Hi!
I'm working on a project where users are interacting with an access database through a collection of userforms in Excel.
I've got an issue with a query that I try to run from a sub routine in excel. The query works fine when i runnit i access but when i try to execute it through a database object in excel(through vba) i receive a runtime error. The error states that there is an undefined function in the expression.
The aim of this query is to append all customers along with a datevalue (selected from a userform) to a table (provided that the record doesn't exist already). The table receiving the records holds customer, date and price.
i've encountered this issue or similar issues several times and would like to find a better solution to it. I feel all my ideas are a bit retarded and imagine that someone here has a way more elegant solution.
The ideas that i have thought of this far is
1) Create a temp table, delete old values, load new values.
Then the date values are available to use in a table and i have no trouble to create the append query
2) Create a VBA procedure where i iterate through the records valid for the date and compare with all customers and then append the ones thar are missing.
3) Create a macro which runs the SQL expression and then create an access application object and run the macro (using DoCmd.RunMacro) through excel (however this would require that all the users has a license for ms access)
Btw i also feel my SQL expression is a bit retarded. First of i tried setting using only a string in the second join criteria but that didn't work. However I did manage to get to it work by concatenate the field with a string and erase the field value.
the vb code where i receive the runtime error
I'm working on a project where users are interacting with an access database through a collection of userforms in Excel.
I've got an issue with a query that I try to run from a sub routine in excel. The query works fine when i runnit i access but when i try to execute it through a database object in excel(through vba) i receive a runtime error. The error states that there is an undefined function in the expression.
The aim of this query is to append all customers along with a datevalue (selected from a userform) to a table (provided that the record doesn't exist already). The table receiving the records holds customer, date and price.
i've encountered this issue or similar issues several times and would like to find a better solution to it. I feel all my ideas are a bit retarded and imagine that someone here has a way more elegant solution.
The ideas that i have thought of this far is
1) Create a temp table, delete old values, load new values.
Then the date values are available to use in a table and i have no trouble to create the append query
2) Create a VBA procedure where i iterate through the records valid for the date and compare with all customers and then append the ones thar are missing.
3) Create a macro which runs the SQL expression and then create an access application object and run the macro (using DoCmd.RunMacro) through excel (however this would require that all the users has a license for ms access)
Btw i also feel my SQL expression is a bit retarded. First of i tried setting using only a string in the second join criteria but that didn't work. However I did manage to get to it work by concatenate the field with a string and erase the field value.
Code:
INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth )
SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput
FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON
CustomerToSAPID.CustumerName
= MontlyResultValues.CustomerName
And
Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01')
= Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD'))
WHERE (((MontlyResultValues.CustomerName) Is Null))
the vb code where i receive the runtime error
Code:
Private Sub ExampleSub()
Dim strSqlCommandText As String
Dim pubDatabaseForPfMng As Database
Set pubDatabaseForPfMng = OpenDatabase("C:\Documents and Settings\" & Get_User_Name & "\Desktop\Example.mdb", False, False, "MS Access;PWD=" & strAccDbPassW & "")
strSqlCommandText = "INSERT INTO MontlyResultValues ( CustomerName, ValidForMonth ) SELECT CustomerToSAPID.CustumerName, #9/1/2010# AS DateForInput FROM CustomerToSAPID LEFT JOIN MontlyResultValues ON CustomerToSAPID.CustumerName = MontlyResultValues.CustomerName And Cstr(Replace(CustomerToSAPID.CustumerName,CustomerToSAPID.CustumerName, '') & '2010-09-01') = Cstr(format(MontlyResultValues.ValidForMonth,'YYYY-MM-DD')) WHERE (((MontlyResultValues.CustomerName) Is Null))"
pubDatabaseForPfMng.Execute strSqlCommandText, dbFailOnError
end sub