Replies

Long time ago there used to be a lot of web sites where you could download “free” icons & other doodads like cursors, animated GIFs, etc. Now, as Hans alluded to, it seems all such sites are infested with viruses, spyware, popups, etc…. …Anyway if interested attached is a zip file w/some “arrow” type icons, bitmaps, and GIFs that I’ve accumulated from various sources over the years. The images are in separate folders based on file type. You may be able to use some of these, or modify as necessary if you have a good icon-editing program (they used to be free, too….)

HTH

For this type of thing, I usually do what Wendell suggests, use comboboxes on form to list valid date ranges for month and year (in some cases, if not all months will have data, the Month combo is “filtered” by the value in Year combo). But if you want to do this using query parameters, you could use something like this example (uses Northwind “Orders” table):

PARAMETERS [Enter Mon/Year (mmm-yy):] Text ( 255 );
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.ShippedDate
FROM Orders
WHERE (((Month([OrderDate]))=IIf(IsDate(“01/” & Left$([Enter Mon/Year (mmm-yy):],3) & “/2000”),Month(“01/” & Left$([Enter Mon/Year (mmm-yy):],3) & “/2000”),0)) AND ((Year([OrderDate]))=IIf(IsDate(“01/01/” & Right$([Enter Mon/Year (mmm-yy):],2)),Year(“01/01/” & Right$([Enter Mon/Year (mmm-yy):],2)),0)))
ORDER BY Orders.OrderDate, Orders.ShippedDate;

In this example the month and year are entered as a text string which is parsed by the two expressions used as WHERE criteria to create constructive dummy dates that are evaluated by Month and Year functions. Note that the two expressions use the same parameter so user only has to enter once. As long as first 3 characters equate to a month and the final 2 characters equate to a valid year the query should work as intended. The following parameter values produced valid results:

jan 98
feb-97
APR98
JAN1997
Dec 97
OCT::1996

These parameter values resulted in an empty result set:

abc 00
xyz 3
?

The convoluted IIf statements and IsDate function are used to avoid dreaded “The expression is too complex to be evaluated” and “Object is no longer valid” error messages. Note I am using a standard US-English installation of Access; it is possible the SQL would have to be modified in some other locales.

HTH

A belated reply, it looks like you resolved this issue, but if interested in example of how to use a multi-select listbox for query criteria, see attached demo database (A2K format). Open frm_Demo, select one or more items in list, then click Open Query button. A query will open, filtered by selected items. This uses technique described in ACC2000: How to Create a Parameter In() Statement, modified somewhat – instead of a parameter, the query (qry_CustomerOrders) has calculated field that calls a function (GetControlVal) that returns the value of specified control on an open form:

InParamEx([Customers]![CustomerID],GetControlVal(“frm_Demo”,”SelectedItems_txt”))

InParamEx is the function based on MSKB article. See code module basInParam and MSKB article for further details. Whenever user selects/unselects items in listbox, an “In” string is generated and stored in textbox (SelectedItems_txt). In actual use, the textbox (or footer) would be hidden from user, visible here for demo purposes.

I’ve used this technique with multiple multi-select listboxes to generate more complex query criteria for reports, export functions, etc, & worked OK. The attached demo is simplified version, using stripped-down versions of the Northwind Customers and Orders tables. You may be able to adapt this somewhat Rube Goldberg-like technique for your own project.

HTH

In belated reply, sorry, do not know of a better way to import the data from web page into Access. In a previous thread (see Re: Importing from Excel (Ex 2002)) described method that can be used to import HTML table into Access – you can’t do this directly (in A2K anyway), you have to download the HTML page to a local folder, then import the downloaded HTML file. I tried this approach with the web site you referenced (the JobServe site) and did not work – got error msg (see attached text file for sample code used). Apparently the way this HTML is formatted does not allow importing – when viewing HTML source text could not identify a TITLE or CAPTION tag for table that could be used for the TransferText method’s HTMLtablename parameter (if left blank, Access tries to import first table found in file – see comments in attached file).

AFAIK, ACC 2000 does not support XML as an import/export format. I’m not currently using ACC 2002 or 2003 so don’t know if XML could be used for this. I recently had to “capture” similar data from a web site that provides “Cost Analysis” data in HTML table format – you input a bunch of parameters & options, submit form, and a HTML page is generated to display results. I wound up cuttin’ ‘n pastin’ results into Excel spreadsheets one at a time…. (fortunately this was a one-time task). Maybe someone more knowledgeable about HTML would have a better solution.

HTH

Excuse the delayed response, but sorry no, I don’t have any brainstorms on how to get around the login/password restrictions. At work I need to export data from various “official” (government) web sites for use in our local databases, all of these sites require some sort of login authentication, passwords, etc, as the data is often sensitive in nature. I’m not smart enough to figure out a good way to automate downloading/exporting data from these sites. (Even if I were, not sure it would be a good idea in view of the information security policies in effect in my organization.) So I usually do the downloading “manually” then at least can somewhat semi-automate the import-into-Access process by using standard download folder locations, file names, etc for each information system/web site used as a data source. Maybe someone more knowledgeable on “web stuff”, posting HTML forms, etc would have some ideas.

HTH

Here is a late reply, the sample code below serves similar purpose only first opens form, then (optionally) searches for a record based on specified key field and value:

Public Sub OpenFormEx(ByRef sFormName As String, _
Optional sOpenArgs As String = "", _
Optional sKeyFld As String = "", _
Optional KeyVal As Variant, _
Optional bClearFilter As Boolean = False)
On Error GoTo Err_Handler

Dim frm As Form
Dim rst As DAO.Recordset
Dim strMsg As String
' sKeyFld = name of Primary Key in table, KeyVal = value to locate

If Len(sOpenArgs) = 0 Then
DoCmd.OpenForm sFormName
Else
DoCmd.OpenForm sFormName, , , , , , sOpenArgs
End If

If (Len(sKeyFld) > 0) And (Not IsNull(KeyVal)) Then
Set frm = Forms(sFormName)

If bClearFilter = True Then
If frm.FilterOn = True Then frm.FilterOn = False
End If

Set rst = frm.RecordsetClone

Select Case TypeName(KeyVal)
Case "String"
rst.FindFirst "[" & sKeyFld & "] = '" & KeyVal & "'"
Case "Integer", "Long", "Single", "Double"
rst.FindFirst "[" & sKeyFld & "] =" & KeyVal
Case "Date"
rst.FindFirst "[" & sKeyFld & "] = #" & KeyVal & "#"
End Select

If Not rst.NoMatch Then
frm.Bookmark = rst.Bookmark
Else
strMsg = "Record not found."
MsgBox strMsg, vbExclamation, "NOT FOUND"
End If
Else
' take no action
End If

Exit_Sub:
Set frm = Nothing
Set rst = Nothing
Exit Sub
Err_Handler:
strMsg = "Error No " & Err.Number & ": " & Err.Description
MsgBox strMsg, vbExclamation, "OPEN FORM ERROR MSG"
Resume Exit_Sub
End Sub

Note that the VBA TypeName function is used to determine the datatype (as string) of the Variant value, and delimits (or doesn’t delimit) the key value as required for search string. I devised this sub because found myself repeating almost identical code over & over to open a form & navigate to specific record. If optional parameters omitted the form simply opens. You may be able to adapt this for a “generic” locate-record function.

HTH

Amanda,

To add to Wendell’s reply, the attached text file is an exported code module that has some functions I use when working with Visual FoxPro (VFP) databases via ODBC. You can import/copy this file into a standard code module to test code. See ImportOrLinkTableODBC function and TestImportTableODBC sub for example of importing (or linking) VFP table. You’ll have to replace the generic parameters in test sub with your actual database path, DSN name etc. If interested there are also functions for creating an ODBC DSN for a specific VFP database programatically, relinking linked VFP tables if the path to the .DBC changes (e.g., from a local to network path), and setting the “Description” property of linked tables (ODBC or otherwise) to reflect the connection string. These can be useful if your Access applications need to interact with FoxPro databases. (Note: The FoxPro applications I work with are written in VFP 6.0).

Also, if you are importing VFP tables that are “cataloged” in a .DBC file (as seems to be case), normally the generic DSN you’d use would be “Visual FoxPro Database” rather than “Visual FoxPro Tables”, which normally connotes a “Free Table Directory”, where you specify a path to folder with “uncataloged” (standalone) .DBF files instead of path to .DBC file. I find it more reliable to create a new DSN for specific VFP database (either programatically or thru user interface). You can then use this DSN name in the code the links or imports the tables.

HTH

…And each word in Stuart’s reply has an “e” in it (including “definitly” which is definitely spelled wrong). In reference to original puzzle, for this type of thing you can cheat by using VBA function like that shown in attached text file. When you run test sub using paragraph text in BigKev’s post, the result is “EJVXZ”. Of course this doesn’t tell you the answer, but gives you a clue if you suspect it concerns some common letter not being used….

…And each word in Stuart’s reply has an “e” in it (including “definitly” which is definitely spelled wrong). In reference to original puzzle, for this type of thing you can cheat by using VBA function like that shown in attached text file. When you run test sub using paragraph text in BigKev’s post, the result is “EJVXZ”. Of course this doesn’t tell you the answer, but gives you a clue if you suspect it concerns some common letter not being used….

Thanx, hope this may be useful….

Thanx, hope this may be useful….

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.

If the number of records to list per listbox is to vary dynamically, one approach would be to create “temporary” queries programatically which would then be used to populate the listboxes on form. As test created some queries “manually” using TOP predicate & subqueries (similar to examples John Hutchinson posted) but this quickly became cumbersome – using NOT IN with more than one subquery resulted in very slow queries even with a small number of records. So instead used DAO methods to create the querydefs to return x number of records, based on user input on form. The function that generates the queries first determines the number of queries to create (based on number of records in table, and on value of x) then runs a loop. Excerpt of code:

For n = 1 To lQueryCount
strSQL = "SELECT TOP " & lngRecords & " ProductID, ProductName " & _
"FROM PRODUCTS WHERE ProductID " & _
"In(SELECT TOP " & CStr((lTotalCount - (lngRecords * (n - 1)))) & " " & _
"ProductID FROM PRODUCTS " & _
"ORDER BY ProductName Desc) " & _
"ORDER BY ProductName;"

Set qry = db.CreateQueryDef("Temp" & n, strSQL)

See attached revised demo database (A2K) for full code and details. Note above, to avoid dreaded NOT IN syntax the subquery selects records from the bottom, not top, calculated number of records, by simply sorting products in descending order in subquery. When function called from form to repopulate listboxes, there is little delay, as might be case if using NOT IN. To test, open Form1, enter valid value in the unbound, unlocked textbox, then click “Reset” button to requery listboxes. If number of queries is less than number of listboxes, code clears the “extra” listboxes. The listbox labels are also updated to reflect which records are listed. If actually using this, may want to add code to clean up the “temp” queries when form closes. Or ditch the querydefs & just use SQL strings as RowSource to populate the listboxes.

Hope this gives you some ideas.

(Edited by MarkD on 23-Nov-04 05:19. Replaced attachment – fixed minor bug in code.)

About the only way you might be able to do this is, is to split the records listed in listbox into separate lists, based on some logical (or illogical) criteria. You could then generate the filter string for report by concatenating the selected items from each list. I usually do this for separate fields but no law says you can’t do it for same field. If interested see attached demo database (A2K format). Demo uses Northwind Products table. Open Form1, select multiple items, then preview or print report (in demo, a crude wizard-generated report). Report will be filtered by selected products from each listbox. Clear the listboxes to view all items in report. In this example I simply broke the products into four groups based on alphabet. You could also use categories or some other criteria. The two textboxes on bottom of form show first, the actual “Where” string used to filter report, the second displays a more user-friendly list of the selected items. In actual use you would not display the first textbox, it would remain hidden to users. See form module for code used to generate the strings. Note the ProductID (number) is used to filter report, not actual product name.

You may be able to adapt this technique for your own project.

HTH

(Edited by MarkD on 23-Nov-04 05:19. Replaced attachment – fixed minor bug in code.)

About the only way you might be able to do this is, is to split the records listed in listbox into separate lists, based on some logical (or illogical) criteria. You could then generate the filter string for report by concatenating the selected items from each list. I usually do this for separate fields but no law says you can’t do it for same field. If interested see attached demo database (A2K format). Demo uses Northwind Products table. Open Form1, select multiple items, then preview or print report (in demo, a crude wizard-generated report). Report will be filtered by selected products from each listbox. Clear the listboxes to view all items in report. In this example I simply broke the products into four groups based on alphabet. You could also use categories or some other criteria. The two textboxes on bottom of form show first, the actual “Where” string used to filter report, the second displays a more user-friendly list of the selected items. In actual use you would not display the first textbox, it would remain hidden to users. See form module for code used to generate the strings. Note the ProductID (number) is used to filter report, not actual product name.

You may be able to adapt this technique for your own project.

HTH