Steven Kogan
Last Activity: Feb 2 '21, 02:03 PM
Joined: Jul 9 '10
Location: Vancouver, BC, Canada
-
Steven Kogan started a topic Built-in filter feature conflict with query criteria based on form fieldin AccessBuilt-in filter feature conflict with query criteria based on form field
I have a form where you can choose to filter results on an unbound field with a Client ID. The form has a query for it's record source that filters on that field if it is filled in. So the where clause has "WHERE qryDetailList01 00Union.ORDClie ntFK=IIf(IsNull ([Forms]![frmDetails]![FilterClient]),[ORDClientFK],[Forms]![frmDetails]![FilterClient])". In the afterupdate event of the filter field I do a me.Requery. That works okay on it's own.... -
In my case the experience is also related to projects and date ranges so they can have the same experience listed several times. I could group by EmployeeID and ExperienceCateg oryID, and then create another query doing what you described. Thanks! -
How would I find records with a specific set of related records?
I'm working on an Employee database and would like to find employees who have experience in several categories. There is an employee table and a related employee experience table. For simplicity let's say the employee table has just the field EmployeeID and the EmployeeExperie nce table has fields EmployeeID and ExperienceCateg oryID. I want to find employees with ExperienceCateg oryID values 2, 3, 8, and 10. Finding an employee with any one of these... -
Here's something odd.
Returns records, and if the field is True, Val(MyBlnField) evaluates to -1.Code:SELECT Val(MyBlnField) FROM MyTable WHERE Val(MyBlnField)=-1
So Access is converting the SQL bit value = 1 to -1, but not in all cases. At least that's what I'm finding...Leave a comment:
-
That seems to be what's happening. It displays -1, but the underlying value is 1. It resolves to True and to 1, but not to -1. For an Access table you can code -1 as if it is True, but for linked tables -1 is not the same as true.
I'd be interested to hear if others get the same result, or if a later patch fixes it so that using -1 as criteria on a linked SQL Server table returns 'True' results (which is actually equal to 1 in the underlying...Leave a comment:
-
I agree with you. It is actually a bit field.
When viewed as a datasheet the values display as 0 and -1. If you use -1 as criteria it returns no records. If you use 1 as criteria it returns records, but displays the values as -1. If you use True as criteria it also returns the records that display as -1.
At least those are my results. I'm not sure if it is my installation.Leave a comment:
-
rst.MoveFirst gives error 3021, no current record.
rst.Count gives method or data member not found.
With an actual linked SQL Server table table changing -1 to True returns records.Leave a comment:
-
SQL Linked table: -1 does not equal True
I notice on my installation that for linked SQL Server tables, -1 does not evaluate the same as true.
Is that correct, or is there a patch that fixes that?
I've tried on 2 systems: one with Access 2003 and another with Access 2010.
When I Google the topic I can't seem to get confirmation.
This kind of code seems to always result in rst.EOF=True if referring to a linked SQL Server table:... -
Steven Kogan replied to Set a date field on unbound form to null with VBA if it has a default date valuein AccessI guess it is because I was running 14.0.6129.5000 (32-bit).
I tried on my home PC and it works as you described, but on my work PC I can't set controls with a default value to null.
I'll look into updating the problem PC.
Thanks!Leave a comment:
-
Steven Kogan replied to Set a date field on unbound form to null with VBA if it has a default date valuein AccessI've Googled the topic and apparently this is how Access 2010 works with unbound controls that have a default value.Leave a comment:
-
Steven Kogan started a topic Set a date field on unbound form to null with VBA if it has a default date valuein AccessSet a date field on unbound form to null with VBA if it has a default date value
I'm using MS Access 2010, and I'm finding that if an unbound control on a form with a date format and a default value of any date is set to null with VBA then it reverts to the default value.
For example I use:
and I get the default date.Code:form_tmpForm.txtDateField=null debug.print form_tmpForm.txtDateField
Is there something wrong with my installation, or is that just how Access 2010 wor... -
Deleting access.qat fixed the problem for me. Thanks!Leave a comment:
-
Thanks for your replies.
When Googling I haven't found posts on someone who had fixed this issue (other than going wired), nor have I found much information on people having problems with Access on a wireless connection. I was hoping there was a trick to make Access work more reliably in a wireless environment.
I haven't been able to recreate the error on Friday, and it seemed there was less network traffic on that day....Leave a comment:
-
It works on wired perfectly.
When it is on wireless it may work for a minute or so, and then it is as if it got disconnected from the network.
Nevertheless, the computer still has a wireless connection, only the mdb doesn't work.Leave a comment:
-
Steven Kogan started a topic What are the recommended requirements for using Jet on a Wireless Network?in AccessWhat are the recommended requirements for using Jet on a Wireless Network?
I have a database that does not work on the client's wireless network. It works fine on a wired connection. Have others found that Access on wireless doesn't work properly? Or has someone found a solution to this problem?
I've done some searching and got some indication that MS Access/JET shouldn't be run on a wireless network. Apparently if the connection breaks for even a split second there is a chance of database corruption.... -
Micho2008, have you tried doing a re-install of MS Access, or trying your database on a different PC?
The problem only occurs on a few computers or installs.
Chances are if you try the same database on a different computer you will be able to view all relationships, however if you put the database back on a problem PC it will crash when opening a relationships window with too many relationships on it. In that case, reduce...Leave a comment:
-
I'm encountering the same problem...Leave a comment:
-
I ran into this problem as well. I automated Excel and the msgbox in Access would not show up. Instead it would be in the background and I had to click MS Access to see it.
To solve this I used AppActivate before the message box:
The message box comes up in Microsoft Access instead of Excel, but at least you can...Code:AppActivate "Microsoft Access" MsgBox "Done"
Leave a comment:
-
The last parameter, "[ID]=" & Me.List0, is the where clause. For this to work ID would be your key field, and the first column in your list box would be the key field. It should filter the records matching the criteria, which may not be what you want to do. If you close the form first it should clear any pre-existing filters.
More generally you would need a way to identify the record, preferably uniquely but not necessarily....Leave a comment:
-
In my case the database relationships worked on a different computer.
I still haven't managed to fix the problem on the original computer though.Leave a comment:
No activity results to display
Leave a comment: