"LastName, Firstname" Trick

Mar 7, 2006

This is a useful trick: If you use a + to concatenate strings then it propogates nulls, if you use a & it doesn't...

So in the expression:

rec![LastName] & (", " + rec![FirstName])

If rec![FirstName] is Null then the expression (", " + rec![FirstName]) will be too, but the rec![LastName] & (Null) part won't be. This neatly drops the comma and space if the firstname is Null

Much better than:

rec![LastName] & iif(isnull(rec![FirstName]), "", ", " + rec![FirstName])

Steve.

View Replies


ADVERTISEMENT

Crazy: FirstName / LastName Set As Primary Key...

Jul 14, 2006

I am looking at a database where the Primary Key is a composite of the customer's First & Last name. Obviously a huge problem! The funny thing is, the table was set up to with an Autonumber CustomerID, so all customers have this information already.

Here's the question: there is a one to many relationship with an orders table, how can I change the primary key from the composite key it uses now, to the autonumber field and not mess up the relationships?

Thanks for help in advance.

View 5 Replies View Related

Prevent Duplicate Dates For Same Firstname And Lastname

Jan 9, 2013

I have tblWO_Points and column with firstname and lastname and dateWO column. What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it wil ask if you want to go to that record?

Example:
Jane Doe Wednesday, January 07, 2012
Jane Doe Tuesday, January 06, 2012
Mike Doe Wednesday, January 07, 2012
Mike Doe Tuesday, January 06, 2012
Mike Doe Thursday, January 12, 2012

So the same dates can occur for different people but the same dates cannot be recorded for the same person.

View 1 Replies View Related

Can You Trick An Autonumber??!!

Aug 26, 2007

Is there a way to do this -

I have surgical operations which are each given an AutoNumber id. To simplify my example - a patient can have a leg operation, an arm operation, a neck operation. All these are linked to the PatientID but they all have a different surgicalID. All these operations are follow-up and the follow-up data is linked to the surgeryID.

I have now realised that the patient may come back for a repeat operation on the leg. Is there a way I can add this operation without it being assigned a newSurgicalID number. Obviously if I use add a new record it gives me a new AutoNumber. Any way around this?

Thanks for any help or suggestions

View 5 Replies View Related

Must Trick To BackColor Textbox

Apr 4, 2006

I open form and get parameter in the openArgs

I have a tabular Form
and I want change the color if the ID is aqual.

for example:
I open the form like this:

DoCmd.OpenForm "frmMessageShow", , , "fID=" & Me.fID, , , Me.AssighmentId
[the Me.AssighmentId is the openArgs]

I want in the second form to color the textbox if AssighmentId = me.openargs

I try do it:
If Me.AssighmentId = Me.OpenArgs Then
AssighmentId.BackColor = vbRed
End If

and its not work :mad:

is there is an option to do it?

thanks!

View 2 Replies View Related

[Trick] Amount In Words

Mar 3, 2005

Make Table Name: tblnum




Fields:

1.num (for number)(Feed 1 to 99)

2.inword (for In words)(Feed One to Ninty Nine)

Make Module



Public Function inwords(amount As Variant) As String




Dim intlac As Integer

Dim intTh As Integer

Dim intHun As Integer

Dim intNum As Integer

Dim strLac As String

Dim strTh As String

Dim strHun As String

Dim strNum As String

Dim intlen As Integer

intlen = Len(amount)

Select Case intlen

Case 7

intlac = Left(amount, 2)

intTh = Mid(amount, 3, 2)

intHun = Mid(amount, 5, 1)

intNum = Right(amount, 2)

strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"

If intTh = 0 Then

strTh = ""

Else

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

End If

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 6

intlac = Left(amount, 1)

intTh = Mid(amount, 2, 2)

intHun = Mid(amount, 4, 1)

intNum = Right(amount, 2)

strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"

If intTh = 0 Then

strTh = ""

Else

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

End If

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 5

intTh = CInt(Left(amount, 2))

intHun = CInt(Mid(amount, 3, 1))

intNum = CInt(Right(amount, 2))

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 4

intTh = CInt(Left(amount, 1))

intHun = CInt(Mid(amount, 2, 1))

intNum = CInt(Right(amount, 2))

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 3

intHun = CInt(Left(amount, 1))

intNum = CInt(Right(amount, 2))

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 2

intNum = CInt(Right(amount, 2))

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

Case 1

intNum = CInt(Right(amount, 2))

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End Select

inwords = strLac & " " & strTh & " " & strHun & " " & strNum & " Only"

End Function
Result = inwords(Number TextBox)

View 2 Replies View Related

Sample Data With FirstName And Surname

May 3, 2005

Hi everyone,

Can someone please help me out here?

I am finishing a project at my client, and they said that I could take a copy of the database I created home (i.e. so that I will support them ;) )

I have a table tblPeople, however, the only condition is that I have to change the First- and LastName columns in this table, since it contains personal information. I need to change the names for about 600 people (400 men, 200 women)

I was thinking of adding the First- and Last Names of movie stars, to keep the flair in my DB :)
Does anyone maybe have a sample database with (movie star) names that I can use to populate my DB?

Thanks in advance,

Jean

View 3 Replies View Related

Front/back End Speed-up Trick - Can Anyone Clarify This?

Nov 23, 2006

I found this snippet in a thread on a forum I don't subscribe to. I think I understand what it's saying but can anyone expand on the how's and why's please?

'Your front end maintains any attachments to the backend (ie first form open opens a recordset on the backend to force the backend ldb file to remain for the session - a known trick to speed up FE-BE setups) '

View 5 Replies View Related

Tried To Execute A Query That Does Not Include The Specified Expression 'LastName' As

Apr 18, 2008

This is the SQL from a query for a Payroll report. It was an already existing report, so I'm trying to modify it to meet the business's needs. The problem is that there are multiple entries for WOLabor.Hours for a given day. These entries come from the invoicing program when a mechanic's hours are logged for billable time. So there may be 3 hours here and 1.5 hours there...but all for the same day. Payroll is just concerned with total hours worked...so I'm trying to combine all the hours for a given day into one variable or something and use that. I know this is long, but apparently a lot of fields are involved:

THIS GIVES ME NO ERRORS:

Code: Original - Code SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode; SELECT Person.LastName, WOLabor.MechanicNo, WOLabor.WONo, WOLabor.Hours, WOLabor.MechanicName, WOLabor.DateOfLabor, WOLabor.LaborRateType, WOLabor.SaleDept, WOLabor.SaleCode, IIf(WoLabor!laborratetype="R",IIf(wolabor!salecode<"P",[Hours],0)) AS Regular, IIf([regular] Is Null,0,[regular]) AS Reg, IIf(WoLabor!laborratetype="P",[Hours],0) AS [Double], IIf(WoLabor!laborratetype="O",[hours],0) AS Overtime, IIf(wolabor!salecode="R",[Hours],0) AS Holiday, IIf(wolabor!salecode="V",[Hours],0) AS LT, IIf(wolabor!salecode="U",[Hours],0) AS npay, IIf(wolabor!salecode="T",[Hours],0) AS Sick, IIf(wolabor!salecode="P",[Hours],0) AS Meet, IIf(wolabor!salecode="S",[Hours],0) AS vac, IIf([Hours]<=8,[Hours],8) AS PayrollReg, IIf([Hours]>8,[Hours]-8,0) AS PayrollOT, SaleCodes.LaborDescription, WOLabor.Cost, WOLabor.Sell, Person.Branch, Branch.Name, Dept.TitleFROM (((WOLabor INNER JOIN SaleCodes ON (WOLabor.SaleBranch=SaleCodes.Branch) AND (WOLabor.SaleDept=SaleCodes.Dept) AND (WOLabor.SaleCode=SaleCodes.Code)) LEFT JOIN Person ON WOLabor.MechanicNo=Person.Number) INNER JOIN Branch ON SaleCodes.Branch=Branch.Number) INNER JOIN Dept ON (SaleCodes.Dept=Dept.Dept) AND (SaleCodes.Branch=Dept.Branch)WHERE (((WOLabor.DateOfLabor)>=[Start Date] And (WOLabor.DateOfLabor)<DateAdd("d",1,[End Date])) AND ((Person.Branch) Like IIf([Enter the Branch Number or "All" for all:] Like "A*","*",[Enter the Branch Number or "All" for all:])) AND ((WOLabor.Transfer)=False))ORDER BY WOLabor.SaleDept, WOLabor.SaleCode;
When I make the following changes to the SELECT section, I get this error:

You tried to execute a query that does not include the specified expression 'LastName' as part of an aggregate function.

Code: Original - Code ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ... ... IIf(Sum([Hours])<=8,Sum([Hours]),8) AS PayrollReg, IIf(Sum([Hours])>8,Sum([Hours])-8,0) AS PayrollOT, ...
Any insight is greatly appreciated.

View 6 Replies View Related

Queries :: Pull Records From 2 Tables If Lastname Is The Same

Nov 19, 2014

I have 2 tables with the same field names, but different data. I need to check if one person is linked to different companies.Both are linked with CompanyID. When I look for a person via the last name (field is called LastName), it should show me from both tables the rows that this person is in. how can I construct this kind of query?

P.S. I want to select all the not just a few from both tables

View 2 Replies View Related

Reports :: Grouping Multiple Fields (Firstname / Surname)

Jan 21, 2015

I have a report with the fields

Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.

On the database people can be entered multiple times so I may have

Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes

Now I now with the group function I could group by the firstname_ field but the results would group like the following

Bob Smith
Bob Smith
Bob Holmes

Jackson Smith
Jackson Smith

Jimmy Carter

Tim Jones

And if I do it by surname, well I am sure you get the drift.

So how can I sort so I would get the following

Bob Smith
Bob Smith

Bob Holmes

etc etc.....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved