Sorry it's been so long for me to notice your reply. . . .
The field Leads.SP is a salesperson name. I want to sequentially select each salesperson from the TABLE Salespeople, assigning the first record to the first lead, second record to the second lead, etc. When I finish sequencing through all the records in the TABLE Salespeople, it should start again from record 1 and continue sequencing until all leads are assigned.
DWolff
Last Activity: Nov 26 '07, 01:21 PM
Joined: Nov 26 '06
Location: South Florida, USA
-
MySQL Query Syntax vs MSSQL
We just converted a database from MySQL 4.1.20 to 5.0 so we can create a view that we may query against. We'd like to use a previous MS Access Query (i.e. MS SQL) to create the View, but are running into syntax errors. I think we've discovered that IIF statements become IF statements, but there are other issues as well. Here is the Access Query. I believe that we also have a problem in the area of "InStr" where we are searching for... -
Well, here we are 2 months later. I now have about 10 people using the form, and through use we've discovere my solution doesn't work if the FNAME field is null. I tried a whole bunch of stuff to no avail. I give up! I'm going back to separate queries by Name and by ID. Extra programming, but at least it works!...Leave a comment:
-
How do I update all records in table 1 by looping through table 2?
My application is to re-assign leads to different groups of salespeople by sequentially assigning them to each salesperson. I've got an Access 2000 front end to an MS-SQL database.
Currently, I do this effectively (but sloppily and slowly) by exporting Access records to Excel, doing my assignments there, importing back into a new work table in Access, and running an update query (joining on Lead.ID). However, I'm soon going to exceed... -
I'm not sure if your problem is with the query to bring back a single sum, or if it is that you cannot bring back JUST the value without all the extraneous information (i.e. month value). It sounds to me like you've successfully queried the Access database from within Excel but can't return the single value. If this is the case, here's how I do it..
Set up a new sheet in your Excel workbook to be used only for returning the summary...Leave a comment:
-
NeoPa and Killer42! I GOT IT! I finally made it work, using a combination of ideas you gave me.
I was never fond of the NZ syntax, because I thought my original code was simpler and just couldn't understand why it wouldn't work. I went back to the message where NeoPa said I should do it with an AND rather than an OR, and applied similar logic to his NZ code as follows:
...Code:WHERE (((ASDS_Leads.ID) Like IIf(IsNull([forms]![Search_Form]![S_ID]),"*",[Forms]![Search_Form]![S_ID]))
Leave a comment:
-
. . . and I now realiz]e that you are helping me from another country. Neat!
Interesting thoughts. What I discovered is that the only way I can make the ID part of the query work from the form is by deleting BOTH the first name and last name criteria. Either one prevents the ID from working.
That gave me another interesting thought. I tried adding a wildcard to the lastname and first name when an ID was selected...Leave a comment:
-
Well, everyone, I tried everything. The "OR" recommendation worked exactly like the "IN" solution. It worked, but was extremely slow in returning the result if an ID was input. My idea of using the form's [S_ID] input in the "IN" didn't work. I also tried playing around with converting the ID to a text field in a few different places (str([ID]) to see if it handled the ID any better as a string rather than integer....Leave a comment:
-
Were it only that simple. The form is very basic with three Text boxes for input of last name (format text), first name (format text) and ID (format long integer). When the user clicks the SEARCH button, it simply opens up another form that gets its data from the query. If the user input data to the name fields, the search form works, if they input data to the ID field, it returns roughly 139,000 of about 165,000 records. It should only return...Leave a comment:
-
This makes a LOT of sense to me! Unfortunately, I don't get to work on my access front end as much as I'd like, so I have to try these things when I get a chance. I'm going to try the NZ function where the null returns: like "*". I'll also try the OR function. I also thought about changing the IN fuction with [Forms]![Search_Form]![S_ID] instead of all [ID].
If anyone cares, I'll report back what I learn (which may not...Leave a comment:
-
If I, in any way, did not seem appreciative, it was certainly not intentional. I SINCERELY appreciate anyone's help and thoughts!...Leave a comment:
-
Very cool. I was unaware of the NZ function. I understand its purpose, though your syntax on the ID field took a bit of contemplation before I understood what it was doing.
The syntax you provided works from both the query and the Search_Form (though I still don't understand why my syntax didn't work from the Form). HOWEVER, it takes an unacceptably long time (about a minute) to get a result if I input an ID (perhaps because it is...Leave a comment:
-
As NeoPa indicated further (above), most of my user community is not up the task of doing the filters. Then they might have to remember to change or delete the filter. That's asking too much!...Leave a comment:
-
Thanks for your help. Please keep in mind two things already mentioned:
1) I tried this with and without the PARAMETERS statement.
2) The query works FINE. It's only when I call it via the Search_Form. The button on the Search_Form opens my Lead_Form, whose data source is this query.
...Code:PARAMETERS [Forms]![Search_Form]![S_Last] Text ( 255 ), [Forms]![Search_Form]![S_First] Text ( 255 ), [Forms]![Search_Form]![S_ID]
Leave a comment:
-
I see no benefit to that, since a the first name criteria is on the same line as the last name criteria, and a null last name is enough to return no values from the name criteria of the query....Leave a comment:
-
Is Unbound Search Form the best solution?
I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious).
I built a data entry form that works fine. Typically, a client will call in and the user has to find his record to add or modify existing data. Originally, I built the form based on a query with an input criteria for the last name and first name. The Last_Name critera is Like [What is Last Name?]&"*"...
No activity results to display
Leave a comment: