Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
Hi All,
I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. Can anyone tell me how to get the import proceedure to see and use the parameter function. I can store the date function in access or excel. Whichever is more efficient. My code is as follows. Public Sub ImportFMC_MC_Data() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim objField As ADODB.Field Dim rsData1 As ADODB.Recordset Dim rsData2 As ADODB.Recordset Dim Param1 As ADODB.Parameter Dim Cmd1 As ADODB.Command Dim lOffset As Long Dim szConnect As String 'Trap any error/exception 'On Error Resume Next 'Body of proceedure. 'Creates and Names 2 Worksheets in the active Workbook. ActiveWorkbook.Sheets.Add Type:=xlWorksheet, Count:=2, after:=Sheets(1) Sheets(2).Name = "FMC Data 4-8 Days" Sheets(3).Name = "MC Status" 'Create the connection string. szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\ERDLogTrack\LogTracker\ERD Logistics Tracker_be.mdb;" 'Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = szConnect 'Create the 1st Recordset object and run the query. Set rsData1 = New ADODB.Recordset rsData1.Open "[qryFMC_Equipment]", szConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable 'Make sure we got records back. If Not rsData1.EOF Then 'Add headers to the worksheet. With Sheets(2).Range("A1") For Each objField In rsData1.Fields .Offset(0, lOffset).Value = objField.Name lOffset = lOffset + 1 Next objField .Resize(1, rsData1.Fields.Count).Font.Bold = True End With 'Dump the contents of the recordset into the worksheet. Sheets(2).Range("A2").CopyFromRecordset rsData1 'Fit the column widths to the data Sheets(2).UsedRange.EntireColumn.AutoFit Else MsgBox "Error: No Records Returned From qryFMC_Equipment.", vbCritical, "ERD GMB" End If 'Close the 1st recordset rsData1.Close Set rsData1 = Nothing 'Create the 2nd Recordset object and run the query. Set rsData2 = New ADODB.Recordset rsData2.Open "[qryMC_Status]", szConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable 'Make sure we got records back. If Not rsData2.EOF Then 'Add headers to the worksheet. With Sheets(3).Range("A1") For Each objField In rsData2.Fields .Offset(0, lOffset).Value = objField.Name lOffset = lOffset + 1 Next objField .Resize(1, rsData2.Fields.Count).Font.Bold = True End With 'Dump the contents of the recordset into the worksheet. Sheets(3).Range("A2").CopyFromRecordset rsData2 'Fit the column widths to the data Sheets(3).UsedRange.EntireColumn.AutoFit Else MsgBox "Error: No Records Returned From qryMC_Status.", vbCritical, "ERD GMB" End If 'Close the recordset rsData2.Close Set rsData2 = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub -- Thanks, Patrick |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
I haven't had a lot of experience with ADO (just fumbling through it the last
few months), but are you trying to use a stored procedure that takes a parameter? The ADODB.Command object has a Parameters property that you set before opening the record set. Here's some code that I use to retrieve stock quotes for a particular date. The query is defined in the database, and it requires a parameter (the date). Function PricesForSpecifiedDate(DBName As String, TheDate As Date) As Variant 'uses parameter query stored in the database Dim Cmd As ADODB.Command Dim Param As ADODB.Parameter Set Cmd = New ADODB.Command Set Param = New ADODB.Parameter With Cmd .CommandText = "Prices_as_of" .CommandType = adCmdStoredProc Set Param = .CreateParameter("Target_Date", adDBDate, adParamInput) Param.Value = TheDate .Parameters.Append Param End With PricesForSpecifiedDate = GetAccessDataFromQuery(Cmd, DBName) End Function Function GetAccessDataFromQuery(Cmd As ADODB.Command, _ DBName As String) As Variant Dim Cnxn As ADODB.Connection Dim rs As ADODB.Recordset Dim v As Variant ReDim v(0, 0) Set Cnxn = OpenConnection(DBName) Cmd.ActiveConnection = Cnxn Set rs = New ADODB.Recordset With rs .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open Cmd If .RecordCount 0 Then v = .GetRows Else v(0, 0) = "No Records" End If .Close End With Cnxn.Close Set Cnxn = Nothing GetAccessDataFromQuery = v End Function 'GetAccessDataFromQuery Function OpenConnection(dBaseName As String) As ADODB.Connection Dim Cnxn As ADODB.Connection Set Cnxn = New ADODB.Connection With Cnxn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = " & XLDocDir & dBaseName .Open End With Set OpenConnection = Cnxn Set Cnxn = Nothing End Function On Thu, 23 Sep 2004 12:09:05 -0700, "PSKelligan" wrote: Hi All, I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. Can anyone tell me how to get the import proceedure to see and use the parameter function. I can store the date function in access or excel. Whichever is more efficient. My code is as follows. Public Sub ImportFMC_MC_Data() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim objField As ADODB.Field Dim rsData1 As ADODB.Recordset Dim rsData2 As ADODB.Recordset Dim Param1 As ADODB.Parameter Dim Cmd1 As ADODB.Command Dim lOffset As Long Dim szConnect As String 'Trap any error/exception 'On Error Resume Next 'Body of proceedure. 'Creates and Names 2 Worksheets in the active Workbook. ActiveWorkbook.Sheets.Add Type:=xlWorksheet, Count:=2, after:=Sheets(1) Sheets(2).Name = "FMC Data 4-8 Days" Sheets(3).Name = "MC Status" 'Create the connection string. szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\ERDLogTrack\LogTracker\ERD Logistics Tracker_be.mdb;" 'Create Command Object. Set Cmd1 = New ADODB.Command Cmd1.ActiveConnection = szConnect 'Create the 1st Recordset object and run the query. Set rsData1 = New ADODB.Recordset rsData1.Open "[qryFMC_Equipment]", szConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable 'Make sure we got records back. If Not rsData1.EOF Then 'Add headers to the worksheet. With Sheets(2).Range("A1") For Each objField In rsData1.Fields .Offset(0, lOffset).Value = objField.Name lOffset = lOffset + 1 Next objField .Resize(1, rsData1.Fields.Count).Font.Bold = True End With 'Dump the contents of the recordset into the worksheet. Sheets(2).Range("A2").CopyFromRecordset rsData1 'Fit the column widths to the data Sheets(2).UsedRange.EntireColumn.AutoFit Else MsgBox "Error: No Records Returned From qryFMC_Equipment.", vbCritical, "ERD GMB" End If 'Close the 1st recordset rsData1.Close Set rsData1 = Nothing 'Create the 2nd Recordset object and run the query. Set rsData2 = New ADODB.Recordset rsData2.Open "[qryMC_Status]", szConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable 'Make sure we got records back. If Not rsData2.EOF Then 'Add headers to the worksheet. With Sheets(3).Range("A1") For Each objField In rsData2.Fields .Offset(0, lOffset).Value = objField.Name lOffset = lOffset + 1 Next objField .Resize(1, rsData2.Fields.Count).Font.Bold = True End With 'Dump the contents of the recordset into the worksheet. Sheets(3).Range("A2").CopyFromRecordset rsData2 'Fit the column widths to the data Sheets(3).UsedRange.EntireColumn.AutoFit Else MsgBox "Error: No Records Returned From qryMC_Status.", vbCritical, "ERD GMB" End If 'Close the recordset rsData2.Close Set rsData2 = Nothing Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
"PSKelligan" wrote ...
I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. You need to understand that your .mdb file is a Jet database. MS Access is not a database; rather it is an application development environment. Certain settings and elements used by the MS Access app are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs, etc but are not otherwise visible. Generally, the only elements visible from outside the MS Access UI are the schema (tables, views, stored procedures, etc), the data in the tables and security elements (users, groups, etc). The only functions that Jet can 'see' are the ones built in, being the VBA5 functions (as distinct from methods). Jet has no knowledge of the user defined functions in the .mdb nor any functions provided by the MS Access UI, including those in the MS Access library (e.g. NZ, EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g. Replace and StrReverse, etc). Similarly, Jet cannot see Excel library functions nor the UDF functions in an Excel workbook. What does your function do? Post the code and someone may be able to suggest a way of achieving the same with sql. You could then wrap in a stored procedure and call the proc with parameters from Excel, using code as posted by Myrna. Actually, the more efficient way may be to call using e.g. .Open("EXECUTE Prices_as_of '" & _ Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';") Jamie. -- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Thanks Jamie and Myrna for your responses.
I did not understand that about Jet -vs- Access. Very enlightening. My function which follows is designed to generate a reporting period the start date is on the 16th of each month and it ends on the 15th of the follwing month. So... on each day I run the query, it will show the period just past. For example if I run the query on August 12th 2004, the RepPeriod wil be 16-June-2004 to 15-July-2004 but on Aug 16th the period will advance 1 month to 16-Jul-2004 to 15-August-2004. If there is a way to accomplish this I would be very greatful for the help. Se function below: Public Function ReportPeriod() As Variant 'Instantiate (define) all variables Dim cDay As Integer 'Current day (sys clock). Dim cMonth As Integer 'Current month (sys clock). Dim cYear As Integer 'Current year (sys clock). Dim sDay As Integer 'Reporting period start day of month (literal). Dim sMonth As Integer 'Reporting period start month (variant). Dim eDay As Integer 'Reporting period end day of month (literal). Dim eMonth As Integer 'Reporting period end month (variant). Dim sDate, eDate As Date 'Start and end of current reporting period (variant). 'Initialize all variables cDay = Format(Date, "dd") 'Sets day of the current date to an interger. cMonth = Format(Date, "mm") 'Sets month of the current date to an interger. cYear = Format(Date, "yyyy") 'Sets year of the current date to an interger. sDay = "16" 'Day of month that reporting period starts. eDay = "15" 'Day of month that reporting period ends. 'Generate start date based on the system clock. If cDay < sDay Then sMonth = (cMonth - 2) Else sMonth = (cMonth - 1) End If sDate = (sMonth & "/" & sDay & "/" & cYear) 'Generate end date based on system clock. If cDay < sDay Then eMonth = (cMonth - 1) Else eMonth = (cMonth) End If eDate = (eMonth & "/" & eDay & "/" & cYear) End Function "Jamie Collins" wrote: "PSKelligan" wrote ... I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. You need to understand that your .mdb file is a Jet database. MS Access is not a database; rather it is an application development environment. Certain settings and elements used by the MS Access app are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs, etc but are not otherwise visible. Generally, the only elements visible from outside the MS Access UI are the schema (tables, views, stored procedures, etc), the data in the tables and security elements (users, groups, etc). The only functions that Jet can 'see' are the ones built in, being the VBA5 functions (as distinct from methods). Jet has no knowledge of the user defined functions in the .mdb nor any functions provided by the MS Access UI, including those in the MS Access library (e.g. NZ, EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g. Replace and StrReverse, etc). Similarly, Jet cannot see Excel library functions nor the UDF functions in an Excel workbook. What does your function do? Post the code and someone may be able to suggest a way of achieving the same with sql. You could then wrap in a stored procedure and call the proc with parameters from Excel, using code as posted by Myrna. Actually, the more efficient way may be to call using e.g. .Open("EXECUTE Prices_as_of '" & _ Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';") Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Also Myrna,
I am working on trying to implement your example. I am definatly way newer to ADO than you are.... Thanks, "PSKelligan" wrote: Thanks Jamie and Myrna for your responses. I did not understand that about Jet -vs- Access. Very enlightening. My function which follows is designed to generate a reporting period the start date is on the 16th of each month and it ends on the 15th of the follwing month. So... on each day I run the query, it will show the period just past. For example if I run the query on August 12th 2004, the RepPeriod wil be 16-June-2004 to 15-July-2004 but on Aug 16th the period will advance 1 month to 16-Jul-2004 to 15-August-2004. If there is a way to accomplish this I would be very greatful for the help. Se function below: Public Function ReportPeriod() As Variant 'Instantiate (define) all variables Dim cDay As Integer 'Current day (sys clock). Dim cMonth As Integer 'Current month (sys clock). Dim cYear As Integer 'Current year (sys clock). Dim sDay As Integer 'Reporting period start day of month (literal). Dim sMonth As Integer 'Reporting period start month (variant). Dim eDay As Integer 'Reporting period end day of month (literal). Dim eMonth As Integer 'Reporting period end month (variant). Dim sDate, eDate As Date 'Start and end of current reporting period (variant). 'Initialize all variables cDay = Format(Date, "dd") 'Sets day of the current date to an interger. cMonth = Format(Date, "mm") 'Sets month of the current date to an interger. cYear = Format(Date, "yyyy") 'Sets year of the current date to an interger. sDay = "16" 'Day of month that reporting period starts. eDay = "15" 'Day of month that reporting period ends. 'Generate start date based on the system clock. If cDay < sDay Then sMonth = (cMonth - 2) Else sMonth = (cMonth - 1) End If sDate = (sMonth & "/" & sDay & "/" & cYear) 'Generate end date based on system clock. If cDay < sDay Then eMonth = (cMonth - 1) Else eMonth = (cMonth) End If eDate = (eMonth & "/" & eDay & "/" & cYear) End Function "Jamie Collins" wrote: "PSKelligan" wrote ... I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. You need to understand that your .mdb file is a Jet database. MS Access is not a database; rather it is an application development environment. Certain settings and elements used by the MS Access app are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs, etc but are not otherwise visible. Generally, the only elements visible from outside the MS Access UI are the schema (tables, views, stored procedures, etc), the data in the tables and security elements (users, groups, etc). The only functions that Jet can 'see' are the ones built in, being the VBA5 functions (as distinct from methods). Jet has no knowledge of the user defined functions in the .mdb nor any functions provided by the MS Access UI, including those in the MS Access library (e.g. NZ, EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g. Replace and StrReverse, etc). Similarly, Jet cannot see Excel library functions nor the UDF functions in an Excel workbook. What does your function do? Post the code and someone may be able to suggest a way of achieving the same with sql. You could then wrap in a stored procedure and call the proc with parameters from Excel, using code as posted by Myrna. Actually, the more efficient way may be to call using e.g. .Open("EXECUTE Prices_as_of '" & _ Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';") Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
Hi, Jamie:
Thanks for your comments, particularly your suggestion re a more efficient method for my particular case. I'll try it. That's for the RS.Open statement, right? (FWIW, it took me 2-3 days of digging into books and the MS Knowledge Base to get the syntax for executing a procedure with parameters!) Maybe you can answer this question about parameter placeholders that stems from article 200190 in the MSKB: "To use a SQL statement with question marks as parameter placeholders, use the same sample code but update the CommandText and CommandType properties as in the following example: objCmd.CommandText = "SELECT * FROM Products WHERE ProductID = ?" objCmd.CommandType = adCmdText " The preceding code adds the parameter and sets its value this way: Set objParam = objCmd.CreateParameter("@productid", _ adInteger, adParamInput, 0, 0) objCmd.Parameters.Append objParam objCmd.Parameters("@productid") = 15 My question involves a query with more than one parameter. Are the parameter values assigned to the question mark placeholders in the order that you added them to the parameters collection? Or can't you use the question mark when there's more than one parameter? Myrna Larson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
I assume you need to return the variables sDate and eDate to the calling
procedure in order to set the values of parameters to the Command object. Since you need 2 return values, I would make this a SUB, not a FUNCTION: Note that, since sDate and eDate are Dim'd as Date variables, I use DateSerial function to assign their values, rather than creating text and forcing VBA to convert that text to a date. Sub ReportPeriod(sDate As Date, eDate As Date) Dim y As Long Dim m As Long y = Year(Date) m = Month(Date) - 1 'previous month If Day(Date) < 16 Then m = m - 1 'subtract another month sDate = DateSerial(y, m, 16) eDate = DateSerial(y, m + 1, 15) End Sub "PSKelligan" wrote in message ... Thanks Jamie and Myrna for your responses. I did not understand that about Jet -vs- Access. Very enlightening. My function which follows is designed to generate a reporting period the start date is on the 16th of each month and it ends on the 15th of the follwing month. So... on each day I run the query, it will show the period just past. For example if I run the query on August 12th 2004, the RepPeriod wil be 16-June-2004 to 15-July-2004 but on Aug 16th the period will advance 1 month to 16-Jul-2004 to 15-August-2004. If there is a way to accomplish this I would be very greatful for the help. Se function below: Public Function ReportPeriod() As Variant 'Instantiate (define) all variables Dim cDay As Integer 'Current day (sys clock). Dim cMonth As Integer 'Current month (sys clock). Dim cYear As Integer 'Current year (sys clock). Dim sDay As Integer 'Reporting period start day of month (literal). Dim sMonth As Integer 'Reporting period start month (variant). Dim eDay As Integer 'Reporting period end day of month (literal). Dim eMonth As Integer 'Reporting period end month (variant). Dim sDate, eDate As Date 'Start and end of current reporting period (variant). 'Initialize all variables cDay = Format(Date, "dd") 'Sets day of the current date to an interger. cMonth = Format(Date, "mm") 'Sets month of the current date to an interger. cYear = Format(Date, "yyyy") 'Sets year of the current date to an interger. sDay = "16" 'Day of month that reporting period starts. eDay = "15" 'Day of month that reporting period ends. 'Generate start date based on the system clock. If cDay < sDay Then sMonth = (cMonth - 2) Else sMonth = (cMonth - 1) End If sDate = (sMonth & "/" & sDay & "/" & cYear) 'Generate end date based on system clock. If cDay < sDay Then eMonth = (cMonth - 1) Else eMonth = (cMonth) End If eDate = (eMonth & "/" & eDay & "/" & cYear) End Function "Jamie Collins" wrote: "PSKelligan" wrote ... I am trying to retrieve records from 2 queries in an Access 2003 database to Excel. The first one works fine but the 2nd is a parameter query and the parameter is answered by a stored function. It defines a reporting period start date based on the system clock. The function works in both access and excel but when the query is run in the follwing code it returns an error state the function is "undefined. You need to understand that your .mdb file is a Jet database. MS Access is not a database; rather it is an application development environment. Certain settings and elements used by the MS Access app are stored in Jet tables in the .mdb file e.g. forms, reports, UDFs, etc but are not otherwise visible. Generally, the only elements visible from outside the MS Access UI are the schema (tables, views, stored procedures, etc), the data in the tables and security elements (users, groups, etc). The only functions that Jet can 'see' are the ones built in, being the VBA5 functions (as distinct from methods). Jet has no knowledge of the user defined functions in the .mdb nor any functions provided by the MS Access UI, including those in the MS Access library (e.g. NZ, EuroConvert, etc) and the VBA6 functions in Access2000 and above (e.g. Replace and StrReverse, etc). Similarly, Jet cannot see Excel library functions nor the UDF functions in an Excel workbook. What does your function do? Post the code and someone may be able to suggest a way of achieving the same with sql. You could then wrap in a stored procedure and call the proc with parameters from Excel, using code as posted by Myrna. Actually, the more efficient way may be to call using e.g. .Open("EXECUTE Prices_as_of '" & _ Format$(TheDate, "yyyy-mm-dd hh:mm:ss") & "';") Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
"PSKelligan" wrote ...
Public Function ReportPeriod() As Variant 'Instantiate (define) all variables Dim cDay As Integer 'Current day (sys clock). Dim cMonth As Integer 'Current month (sys clock). Dim cYear As Integer 'Current year (sys clock). Dim sDay As Integer 'Reporting period start day of month (literal). Dim sMonth As Integer 'Reporting period start month (variant). Dim eDay As Integer 'Reporting period end day of month (literal). Dim eMonth As Integer 'Reporting period end month (variant). Dim sDate, eDate As Date 'Start and end of current reporting period (variant). 'Initialize all variables cDay = Format(Date, "dd") 'Sets day of the current date to an interger. cMonth = Format(Date, "mm") 'Sets month of the current date to an interger. cYear = Format(Date, "yyyy") 'Sets year of the current date to an interger. sDay = "16" 'Day of month that reporting period starts. eDay = "15" 'Day of month that reporting period ends. 'Generate start date based on the system clock. If cDay < sDay Then sMonth = (cMonth - 2) Else sMonth = (cMonth - 1) End If sDate = (sMonth & "/" & sDay & "/" & cYear) 'Generate end date based on system clock. If cDay < sDay Then eMonth = (cMonth - 1) Else eMonth = (cMonth) End If eDate = (eMonth & "/" & eDay & "/" & cYear) End Function I think you must have snipped something because your function as posted has no return value. Jamie. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
"PSKelligan" wrote ...
Public Function ReportPeriod() As Variant 'Instantiate (define) all variables Dim cDay As Integer 'Current day (sys clock). Dim cMonth As Integer 'Current month (sys clock). Dim cYear As Integer 'Current year (sys clock). Dim sDay As Integer 'Reporting period start day of month (literal). Dim sMonth As Integer 'Reporting period start month (variant). Dim eDay As Integer 'Reporting period end day of month (literal). Dim eMonth As Integer 'Reporting period end month (variant). Dim sDate, eDate As Date 'Start and end of current reporting period (variant). 'Initialize all variables cDay = Format(Date, "dd") 'Sets day of the current date to an interger. cMonth = Format(Date, "mm") 'Sets month of the current date to an interger. cYear = Format(Date, "yyyy") 'Sets year of the current date to an interger. sDay = "16" 'Day of month that reporting period starts. eDay = "15" 'Day of month that reporting period ends. 'Generate start date based on the system clock. If cDay < sDay Then sMonth = (cMonth - 2) Else sMonth = (cMonth - 1) End If sDate = (sMonth & "/" & sDay & "/" & cYear) 'Generate end date based on system clock. If cDay < sDay Then eMonth = (cMonth - 1) Else eMonth = (cMonth) End If eDate = (eMonth & "/" & eDay & "/" & cYear) End Function I think you must have snipped something because you function as posted has no return value. However, I guess you require the following sql: SELECT DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,2,1),16) AS start_date, DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,1,0),15) AS end_date ; Jamie. -- |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
Myrna Larson wrote ...
Thanks for your comments, particularly your suggestion re a more efficient method for my particular case. I'll try it. That's for the RS.Open statement, right? Yes, you can use EXECUTE syntax in RS.Open's Source argument FWIW, it took me 2-3 days of digging into books and the MS Knowledge Base to get the syntax for executing a procedure with parameters! I agree the Command/Parameters syntax is not particularly intuitive which is probably one reason I avoid using it, but my main reason is that I can't help but wonder if all the jumping through hoops merely results in an EXECUTE statement being passed to the provider anyhow, so I may as well write that EXECUTE statement myself. Are the parameter values assigned to the question mark placeholders in the order that you added them to the parameters collection? Or can't you use the question mark when there's more than one parameter? I don't know the answer to that one because I don't use question mark placeholders. What is the advantage over using named arguments? e.g. CREATE PROCEDURE MyStoredProc ( start_date DATETIME, end_date DATETIME ) AS SELECT RefID, DateEffective, Earnings FROM EarningsHistory WHERE DateEffective BETWEEN start_date AND end_date ; Jamie. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Myrna and Jamie,
Wow! I have obviously gotten in way out of my depth. SELECT DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,2,1),16) AS start_date, DATESERIAL(YEAR(NOW()),MONTH(NOW())-IIF(DAY(NOW())<16,1,0),15) AS end_date ; What would the syntax of the .open meathod be? Would it work to make the a subquery that I should put in the stored query? This way I would just call the stored query and it should run just like the other 'Non-Parameter' queries I call. Thanks so much for the help, |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Hello again,
I tried to add your sql to my query and it seemed to work ok except for the -IIF portion of the statement. Here is my statement: SELECT * FROM tblHistory WHERE (((tblHistory.date_Updated) Between DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15))) ORDER BY tblHistory.date_Updated DESC; Any syntax isues that are preventing this? Your little bit of sql, if it will work in this statement will make all my problems go away. Thanks, Patrick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
If it is "almost" working, then it looks to me as if you've introduced a bug
in Jamie's code. For the first Iff function, since it's -Iff (rather than +Iff) the 2 and 1 inside the parentheses should be positive, not negative. If you make them negative you are adding 2 or 1 month instead of subtracting. FWIW, When I was doing queries by passing the SQL string, I had to pass dates in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing a date variable as such didn't work for me. So my code (in part) looked like this: Dim SQL As String SQL = "SELECT PrTicker, PrNAV FROM Prices WHERE PrDate = DDD" & _ " ORDER BY PrTicker" SQL = Replace(S, "DDD", SQLDate(TheDate)) The function to format the date correctly, SQLDate, looked like this: Function SQLDate(ADate As Date) As String SQLDate = Format$(ADate, "\#mm/dd/yyyy\#") End Function I like to write out the SQL statement using "place holders" for the variables, then use the Replace function to insert the correct values. I would write your code as Dim sDate As String Dim eDate AS String Dim SQL As String sDate = SQLDate(DateSerial(Year(Now()), _ Month(Now()-IIf(Day(Now())<16,2,1),16)) eDate = SQLDate(DateSerial(Year(Now()), _ Month(Now()-IIf(Day(Now())<16,1,0),15)) SQL = "SELECT * FROM tblHistory WHERE (tblHistory.Date_Updated) Between SDATE and EDATE ORDER BY tblHistory.date_Updated DESC;" SQL = Replace(SQL,"SDATE",sDate) SQL = Replace(SQL,"EDATE",eDate) Then open the record set with the SQL statement. On Mon, 27 Sep 2004 09:29:02 -0700, "PSKelligan" wrote: Hello again, I tried to add your sql to my query and it seemed to work ok except for the -IIF portion of the statement. Here is my statement: SELECT * FROM tblHistory WHERE (((tblHistory.date_Updated) Between DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15))) ORDER BY tblHistory.date_Updated DESC; Any syntax isues that are preventing this? Your little bit of sql, if it will work in this statement will make all my problems go away. Thanks, Patrick |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Myrna,
Yes, the -2, -1 actually were just my attemts at troubleshooting the code. It did not work with or without it. It querys alright but does not roll back to previous months. tripple checked my parenthasies but no clue as to why it isnot worki Thanks, Patrick "Myrna Larson" wrote: If it is "almost" working, then it looks to me as if you've introduced a bug in Jamie's code. For the first Iff function, since it's -Iff (rather than +Iff) the 2 and 1 inside the parentheses should be positive, not negative. If you make them negative you are adding 2 or 1 month instead of subtracting. FWIW, When I was doing queries by passing the SQL string, I had to pass dates in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing a date variable as such didn't work for me. So my code (in part) looked like this: Dim SQL As String SQL = "SELECT PrTicker, PrNAV FROM Prices WHERE PrDate = DDD" & _ " ORDER BY PrTicker" SQL = Replace(S, "DDD", SQLDate(TheDate)) The function to format the date correctly, SQLDate, looked like this: Function SQLDate(ADate As Date) As String SQLDate = Format$(ADate, "\#mm/dd/yyyy\#") End Function I like to write out the SQL statement using "place holders" for the variables, then use the Replace function to insert the correct values. I would write your code as Dim sDate As String Dim eDate AS String Dim SQL As String sDate = SQLDate(DateSerial(Year(Now()), _ Month(Now()-IIf(Day(Now())<16,2,1),16)) eDate = SQLDate(DateSerial(Year(Now()), _ Month(Now()-IIf(Day(Now())<16,1,0),15)) SQL = "SELECT * FROM tblHistory WHERE (tblHistory.Date_Updated) Between SDATE and EDATE ORDER BY tblHistory.date_Updated DESC;" SQL = Replace(SQL,"SDATE",sDate) SQL = Replace(SQL,"EDATE",eDate) Then open the record set with the SQL statement. On Mon, 27 Sep 2004 09:29:02 -0700, "PSKelligan" wrote: Hello again, I tried to add your sql to my query and it seemed to work ok except for the -IIF portion of the statement. Here is my statement: SELECT * FROM tblHistory WHERE (((tblHistory.date_Updated) Between DateSerial(Year(Now()),Month(Now()-IIf(Day(Now())<16,-2,-1)),16) And DateSerial(Year(Now()),Month(Now())-IIf(Day(Now())<16,1,0),15))) ORDER BY tblHistory.date_Updated DESC; Any syntax isues that are preventing this? Your little bit of sql, if it will work in this statement will make all my problems go away. Thanks, Patrick |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct) v.20
Myrna Larson wrote ...
I don't know the answer to that one because I don't use question mark placeholders. What is the advantage over using named arguments? I don't know the answer to your question as to "Why". I think that using the question marks, particularly if there's more than one parameter, obfuscates the code. A procedure with arguments that make it difficult and unpredictable for a client app to use? I don't think that's one of the goals of obfuscation <g. Jamie. -- |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
"PSKelligan" wrote ...
I tried to add your sql to my query and it seemed to work ok except for the -IIF portion of the statement. Any syntax isues that are preventing this? Your little bit of sql, if it will work in this statement will make all my problems go away. I think you have a paren in the wrong place and some unwanted minus signs. Try: SELECT * FROM tblHistory WHERE date_Updated Between DateSerial( Year(Now()), Month(Now())-IIf(Day(Now())<16,2,1), 16) And DateSerial( Year(Now()), Month(Now())-IIf(Day(Now())<16,1,0), 15) ORDER BY tblHistory.date_Updated DESC ; Jamie. -- |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
Myrna Larson wrote ...
FWIW, When I was doing queries by passing the SQL string, I had to pass dates in this format: #mm/dd/yyyy#, i.e. #09/27/2004#, with the pound signs. Passing a date variable as such didn't work for me. The problem with that format is it is treated inconsistently by Jet e.g. it sees #27/09/2004# as being the same as #09/27/2004# but #01/04/2004# and #04/01/2004# as being different. Using an unambiguous format (e.g. dd mmm yyyy) seems to be the solution but using a word to represent the month could introduce in language problems. I've come to the conclusion that the best date format for Jet is 'dddd-mm-dd' being the international standard (ISO-8601). In this format, Jet handles ambiguous dates consistently e.g. 2004-04-01 is always 1st April 2004 and 2004-24-01 is always rejected rather than being coerced to 24th January 2004. For general advantages of this date format see: http://www.cl.cam.ac.uk/~mgk25/iso-time.html I like to write out the SQL statement using "place holders" for the variables, then use the Replace function to insert the correct values. It's worth stressing what a fine idea that is. Jamie. -- |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Access records to excel (parameter is a called funct)
"PSKelligan" wrote ...
tripple checked my parenthasies Just to clarify, than than your Month(Now() - <<the IFF stuff ) you instead require Month(Now()) - <<the IFF stuff Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import Access records into Excel | Excel Discussion (Misc queries) | |||
Linking parameter query from Access to pivot table in Excel | Excel Discussion (Misc queries) | |||
import data from an Access parameter query | Excel Discussion (Misc queries) | |||
Import records from Access to Excel | Excel Discussion (Misc queries) | |||
I can import Access Tables. But, I can't import Access queries | Excel Programming |