Trouble Flipping Name Field In Query!!!!
Oct 9, 2007
:mad:Hello I'm new to queries!
I'm having a problem writing an update query that will look at a name field ie (Dan Wilson Sr) and flip the name to a shortname ie (Wilson Sr Dan) with the criteria based to only flip the name if it contains Jr or Sr.
Below is an example of a select query I was using to test before updating my table.
SELECT *
FROM [Goodrec - All]
WHERE (instr(shortname,' JR')>0 Or instr(shortname,'SR')>0 Or instr(shortname,' III')>0) And instr(shortname,' MD')=0;
This query provides the following result for example, if name is "PAUL D TAYLOR SR" it is flipped to "SR PAUL D TAYLOR." I need shortname to display it like this "TAYLOR SR PAUL D"
Thanks in advance for any assistance!
View Replies
ADVERTISEMENT
Jun 30, 2005
Hi all,
Been pulling my hair out about this for a while, so i hope someone's feeling kind:
http://www.agga.dsl.pipex.com/pics/AWF01.gif
I'm using the BHRefPrefix to build part of a reference number. The Principal and Site exist on a separate form, and i need to use both as criteria in order for the correct BHRefPrefix to be returned.
I sort of get it working using two joins, but it doesn't let me add any new records. I've tried straight queries, queries with DLookup expressions, and i just can't get what i need.
Pete
View 3 Replies
View Related
May 15, 2006
How do you use the "sqrt"-function in a query? This function is unavailable in a query - and i do not know why. Please help!?
Thanks
View 2 Replies
View Related
Feb 15, 2008
I have attached a small db that has 1 query with several fields..
Here is what I'm looking to do.
In the Query there is the following fields:
SignINMemberID
GuestOfMember
SignInDateTime
AllowedEntry
Now all these are derived from tdatMemberSignIn.
What I want to do is show the name that corresponds with the MemberID in table MemberList.
That means if the SignInMemberID exists it would show First and Last Name and if GuestOfMember is blank it would show nothing.
If the SignInMemberID doesnt exist but the GuestOfMember does then it would show the First and Last name beside that field.
If the number doesnt exist it would be blank.
I entered the places I wanted to show the data in the query for your reference as expr1, expr2, expr3, expr4 for your reference.
Any help would be appreciatted.
View 1 Replies
View Related
Oct 4, 2004
I have set up a query to return the records of pupils with certain information, most of this information comes from a subform, (i have set up a form with multiple subforms to make data entry easier for the user) with these subforms many records have multiple subform records (i.e. Joe Bloggs has 3 different Approved hosts and 3 approved drivers) when i try to make a report it wants to print the same data 9 times (because there are 9 differnet combinations i asume) what i want to to is have the approved drivers in one list box and the approved hosts in another list box and have the report print only one!
can anyone help me!
please!
View 1 Replies
View Related
Feb 8, 2006
Trying to create this thing is tying my brain in knots, so I'm back for help. Hopefully I'm missing something basic:I've finally been able to normalize my database by taking it offline for a few weeks, and already the amount of duplicative/erroneous data that has been cleaned up is staggering. Right now I'm trying to ensure that our data is as complete as possible in the following circumstance: tableGroups has PK GroupID tablePeople has PK PersonID tableRelationships creates a many-to-many relationship between the two, via PersonIDLookup and GroupIDLookup. It also has its own Autonumber PK. tableRelationships also has fields that describe the relationship: For example, FormerRelationship, MailingContact, and PublicContact are all boolean fields. What I want to do is get a list of all GroupIDs which do not have any entry that is marked PublicContact. Problem is that some may have Former contacts, or privately listed ones for internal office use only; over time our Public Contacts move, die, quit, etc and sometimes they have not gotten replaced. I'll need to do the same thing for MailingContact as well, but the procedure for one should be the same as the other. No one who is a Former contact should also be listed as Public/Mailing, but I can't promise the data is that clean at this point.The only thought that occurs to me right now is to make one query that shows GroupIDs that do have at least one matching relationship, and then create a Find Unmatched query that relates to that. Is there something simpler with only one query?
View 3 Replies
View Related
Feb 16, 2006
This is my first time posting here and I hope you guys can help me.
I have a problem with using a crosstab query.
My original table looks something like this
VALUES
Oil_ID (PK)
Property_ID (PK)
Col_Heading (PK)
Row_Heading (PK)
Values
Oil Name
Property Name
I made a crosstab query with the columns being 'Col_Heading' and the rows being 'Row_Heading' and the values being 'Values'.
I made a form that has two combo boxes (Oil Name, Property Name). It also has a subform in which I want to display the crosstab query.
Now here comes the problem. Those two combo boxes need to be able to filter the data, but I am not able to put the code:
IIf(IsNull([Forms]![Values Form]![combo52]),[VALUES].[Oil Name],[Forms]![Values Form]![combo52])
IIf(IsNull([Forms]![Values Form]![combo66]),[VALUES].[Property Name],[Forms]![Values Form]![combo66])
in the Criteria because an error pops up saying: "The Microsoft Jet database engine does not recognize 'above_code' as a valid field name or expression.
So I created a select query, call it QueryA', that would let me filter the data. The I created a crosstab query based on QueryA, but the same error message occurs.
I am stuck. If you could help me, I would greatly appreciate it.
Thanks
View 1 Replies
View Related
Nov 20, 2006
I have a union query that will run from the SQL design window but if I try and run it from the database window it crashes Access (2003). Any ideas?
SELECT [TestLat]-0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT [TestLat]+0.5 AS Lat, tblTestData.TestLong AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]-0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData
UNION SELECT tblTestData.TestLat AS Lat, [TestLong]+0.5 AS [Long], tblTestData.TestTemp
FROM tblTestData;
View 4 Replies
View Related
Jun 12, 2007
I am having trouble with some code I am using on a query. Part of the query comes from two fields in a form. The rest comes from a table. The query then populates a report. The code I have written works fine. The trouble starts when I try and enter in some code so that if some of the fields in the form is left blank, it just returns values based on the fileds that has data, so ignores any blank fields.
I have tried to use examples illustrated in this Forum, but it does not seem to like it. I keep on getting the request to fill in the parameter value. I am sure it is something simple!
This is the code I have entered for the Invoice Date Field:
Between [Forms]![Invoices]![Invoice date 1] And [Forms]![Invoices]![Invoice date 2]
(this is so the user can enter a date range with the two fields)
View 14 Replies
View Related
Nov 11, 2007
Hello everybody,
I am having trouble with this criteria on one field in a query
<>19 Or <>15 Or <>14 Or <>16 Or <>17 Or <>1 Or <>3
The query is still showing records that contain 14, 3 and 16.
Basically, I don't want to show records containing 19, 15, 14, 16, 17, 1 and 3.
Thanks
View 3 Replies
View Related
Feb 10, 2006
I have a query called VOLTEST that is not bound to the form. VOLTEST sums a field in a table from a subform. When the input is finished in the subform and control goes back to the main form, I want the total from the VOLTEST to show in a field on the form that is not a tab stop. Basically, this is just for reference.
I have tried to put =[VOLTEST]![SumOftest_value] in the control source of the field on the form but I keep getting "Name?" as the result instead of the sum total.
I can run the query in the gotfocus of the first field in the tab order after the subform and I get the correct result in a datasheet view. So, I know the query is working correctly. I just need it to show the result in field on the form.
Thanks for your help. GG
View 2 Replies
View Related
Feb 2, 2007
I am creating a database for a workplace for staff management.
i want a query where the user can input a month or a date range and be able to view all of the staff on holiday during this period.
I originally had a working query however it ignored staff that were on holiday during this period for example searched 11/2006
showed holidays in november but not staff who are on holiday from before and still on holiday during this time.
At this moment in time the fields i have included for beginning the query is BookingID, StaffID, EventName,StartDate,EndDate
any help would be appreciated
View 1 Replies
View Related
Oct 8, 2007
In MS Access 97 I have a basic query based on a single table. When I use "Between [Start Date] and [End Date]" as the criteria in a date field it returns incorrect records.
It works correctly on date fields that are not calculated but not on the date field that is calculated. If I type in the actual dates in the criteria (instead of the prompts) it returns the correct records.
Is there a solution to this behaviour? I need the users to be able to input the dates.
Thanks.
P.S. I have tried using the base query as a subquery but I still have the same issue in the second query based on the subquery.
View 5 Replies
View Related
Jun 20, 2005
Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:
tb_staff containing staff_id, first_name, last_name, base, dept_ward, job_title
tb_courses containing course_id and course
tb_training_done containing staff_id, course_id, date_trained, trainer, venue
I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.
Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?
Hope that's not too vague?
View 2 Replies
View Related
Aug 6, 2007
im having a bit of trouble generating a field in one of my queries. i think its fairly simple, all i want to do is have the field in my query to show a date, that is just calculated by subtracting 5 days from the current day. i have something that looks like =Now()-5 but that doesnt work obviously. so im just asking whats the imput going to look like if you want to subtract 5 days from a date? oh and im using short dates for the date inputs
View 2 Replies
View Related
Sep 13, 2004
I am trying to build a report that lists all call in's and all tardies on the same sheet. They are two different tables, that I made queries out of because of calculation fields. Do you know how I can include all the needed data in the same report?
What I'm getting when I try is either all call-in's for each name that has tardies or vice versa. I need to see all data on one report
The reason I didn't just put all the data in one table was because we track call-in's by occurence, but we track tardies by pay period. So each call in is logged in the call-in table with the date it happened on, and the tardies are logged in by pay period, so only the total is stored in the field.
Do you have any ideas? I'm really stuck and I've been workin' on it for three days now and I don't think I'm going to find the solution by myself. Sorry. Thanks!
KellyJo
View 5 Replies
View Related
Jul 5, 2005
Gee, I know this is probably so simple...I've never in my life worked with any kind of data base program. I've spent 2 hours trying to make it work and it doesn't. I've tried so many things that I can't even remember what steps I did. I'm trying to create a one column list for phone numbers, no names or anything else...the most important thing is that when I add new phone numbers to this list I want Access to alert me that it is a duplicate and not allow me to add it.....first of all it won't let me add entries with dashes such as 312-5964, I tried selecting "numbers" and also "auto number", both didn't work, what am I doing wrong...can someone give me the steps from the beginning please....I don't have time to figure this out and it's holding me back from finishing a task. I have to get working on my Video Professor Learning CD for Access which I have at home, but right now I'm at work and can't do that...hellllllllp! I'm ready to tear my hair out :eek:
Thanks, Rosey
View 2 Replies
View Related
Mar 13, 2006
I have an Access DB I have to put on numberous laptops. The DB is being transported via a CD. When i try to copy the DB from teh CD to a new folder on the laptop I get messages that the DB may have a virus and it cant be copied, or I need to install a microsoft service bulliten...xxxx ( I am not sure what the number is). The DB does contain VBA code and I am certain it is virus free. Is there some setting in access which enables this file to be copied on laptops which do contain virus or firewall protection or must I disable virus protection on these machines before instlaling the program or is it another issue altogether. Thanks.
View 3 Replies
View Related
Mar 27, 2006
Hi
I have genreated an autonumber in my table, at this is used as key. after importing 135 records it starts to jump 48 records before it creates a new. This means that with every record i import after 135 i have a space of 48 between each. So what sort of thing can cause this? and even better how do i fix it?
Best Regard
Roald
View 2 Replies
View Related
Jul 6, 2006
I have been using the forums for the past month and a half, and they have been extremely beneficial to me. I am working on a database that cross references my companies Steel standards to American standards. I am trying to make it searchable in a couple of different ways. I have borrowed code found on this site to make two different search engines. One works very well but the other I am getting hung up when I try to switch from the previous code to mine. below is an example of my code. It is taken from code placed on here previous called "SampleSearch"
Private Sub cmdSearch_Click()
On Error Resume Next
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'tblSubject qrySearchCriteriaSub
If Me![Spec] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Spec = """ & [Spec] & """"
End If
If Me![SteelType] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.SteelType like """ & [SteelType] & "*"""
End If
If Me![Group11] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group11 like """ & [Group11] & "*"""
End If
If Me![Group143] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Group143 like """ & [Group143] & "*"""
End If
If Me![Substitute1, Substitute2, Substitute3, Substitute4, Substitute5, Substitute6, Substitute7, Substitute8, Substitute9] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Substitute1 = """ & [Substitute1] & """"
End If
sSql = "SELECT DISTINCT [Spec], [SteelType],[Group11],[Group143]from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
'[Spec], [SteelType],[Group11],[Group143]
'frmSearchCriteriaSub.Requery
End Sub
I apologize also because I do not know how to post this in a scrollable window. If someone could help me with that too.
The problem with the code is that I am using a form to filter a query, this will then display the table of the steels that meet the requirements. However, I have altered all of the fields to my names and the combo boxes have my information, but they do not seem to place any parameters on the query. I attached the original DB that I got this code from any help would be appreciated.
Riley
View 2 Replies
View Related
Apr 5, 2007
I have a access 2002 database that was handed down to me.. this program does some document control, set up with access rights for different users.I got into it to clean it up a bit and I've hit a snag. I converted the database from a 2000 to a 2002. Amidst playing around with the Log-In screen somehow my Log In button stopped working; except if you type your password and then hit enter.Now the Log In button ( cmdLogIn_Click() ) is pointing at the same exact script as the ( txtpassword_KeyDown(KeyCode As Integer, Shift As Integer) ) function.I don't know any VB and I'm not really familiar with Access, so if anyone could decode this for me I would be really grateful. Here is the script that those two functions are pointing to:Private Sub LogIn_Click() Dim strPassword As String Dim strPasswordAttempt txtPassword.SetFocus strPasswordAttempt = txtPassword.Text cboUsername.SetFocus strPassword = DLookup("[Password]", "User", "[Username] = '" & cboUsername.Value & "'") If strPasswordAttempt = strPassword Then strUsername = cboUsername.Value intAccessLevel = DLookup("[AccessLevel]", "User", "[Username] = '" & cboUsername.Value & "'") bolApprover = DLookup("[Approver]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditDocumentInfo = DLookup("[EditDocumentInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditUserInfo = DLookup("[EditUserInfo]", "User", "[Username] = '" & cboUsername.Value & "'") bolEditReferenceDocuments = DLookup("[EditReferenceDocuments]", "User", "[Username] = '" & cboUsername.Value & "'") bolViewAllApprovers = DLookup("[ViewAllApprovers]", "User", "[Username] = '" & cboUsername.Value & "'") bolDocumentControl = DLookup("[DocumentControl]", "User", "[Username] = '" & cboUsername.Value & "'") ' MsgBox "Your access level is " & intAccessLevel Startup ' run startup sub DoCmd.OpenForm "Welcome", acNormal DoCmd.Close acForm, "Login" LogEvent strUsername, Now(), "Login Successful", "Network Username: " & strNetworkUserName & " on Computer " & strComputerName 'LogEvent strUsername, Now(), "Login Successful", " on Computer " & strComputerName Else MsgBox "That password is incorrect. Try again." LogEvent "Unknown", Now(), "Login Failed", "Attempt: " & CStr(cboUsername.Value) & " , " & strPasswordAttempt & " Network UserName: " & strNetworkUserName & " on computer " & strComputerName End If End SubWhen you click the Log In button it does actually run the script, but it gives me the "The password is incorrect. Try again." message even if the password is correct. I can just click into the txtpassword box and hit enter and it will log me into the database.P.S. I say again, this isn't my code.. its probably super ugly, but I cant' tell :o
View 3 Replies
View Related
May 1, 2007
Hello all active members of this forum,
Like I stated on the title, this forum got me into trouble. By following the reading in this forum and some recommendations from the regular users I have created thre database for the place I work at. now they want me to be the official DB admin! :eek:
So now I'm coming back to you guys for more advice... :rolleyes: what reading should I do to better my Access knowledge over all.
Any recommendation is welcome.
I have already contacted th local college and they do have a distant learning class for Access and I will be taking it the next semester, but I would like to do some reading into becoming a REAL Access programmer with a solid foundation.
There must be some good books out there to purchase to get me started. So all are welcome to give me some feedback.
Thanks!
René
View 14 Replies
View Related
Sep 6, 2007
Access 2002.
Hey guys-
I have a software program that I am exporting data out of into excel (it's SQL based). From Excel, I then am importing into Access into an existing table previously created from a similar file (same columns, just different record sets). I am running into problems when importing files...
If I import into a new table, and have Access create the table based off of the Excel file- it works fine. If I try to import more records into this same table, it tells me it failed to import and that an error occured (no error's table created, no error number given). If I delete the original records in the table, and re-import the SAME RECORDS that were originally imported to create the table- it doesn't work. It will only import my files if it creates a brand new table for each import, every time.
Can Access not import into an existing table? It should- but I dont get what's going on here. I have tried DBF, HTML, txt files- all the same results. I really don't want to have to create a new table each time- as now my Queries will have to be updated everytime I create a new table. Can you please help?
View 1 Replies
View Related
Dec 16, 2005
I don't how this happened but a crucial field (ClientID) in my Clients table changed from Autonumber to Number. It's the primary key and linked to other tables. Access won't let me change it back...and until i do my entire database is at a standstil - cannot enter new clients.
View 2 Replies
View Related
Dec 11, 2006
Can someone please help me, I might have my logic a little backwords.
I have a table (tblLoanDetails)
LoanNumberID
etc.
etc.
etc.
EscrowCompanyID
then I have a table called (tblEscrowCompanies)
EscrowCompanyID
EscrowCompanyName
EscrowCompanyAddress
etc.
etc.
EscrowOfficerID
then I have a table called (tblEscrowOfficers)
EscrowOfficeID
EscrowOfficerName
EscrowOfficerSalutation
EscrowOfficerEmail
What I am trying to do is when someone is filling out the first table (tblLoanDetails) they have a dropdown to choose the escrow company, then they must choose the escrow officer
Each escrow office can have many officers, however, when they choose the escrow officer from a dropdown, I only want that offices escrow officers to appear in the dropdown.
Any help would be greatly appreciated
thank you
Josh
View 1 Replies
View Related
Jan 17, 2008
I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are:
tbl_workorders (main project info)
tbl_services (services ordered)
tbl_contacts (internal company contacts)
tbl_customers (customer information)
tbl_project_dates (milestones of project)
Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders).
Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables.
Thanks for your feedback!
View 12 Replies
View Related
