Concatenate two DLookup in ApplyFilter


Hi,

I have a form bound to a table and I try to filter the reccords in the open form event.

My problem is that the field that supports the filter : "strCréateur" (creator in french) is a combination of to fields in an other table "strNom" (LastName) and "strPrénom" (LastName)

In addition to this I use DLookup to automaticaly get the Last and FirstName in the other table called "tblUsers" where they have to match environ("username") (I have access 2002).

I don't know if what I said is understandable so I'll let the code speak for itself

Here are my non working shots :


'FILTER IN ORDER TO DISPLAY ONLY THE RECCORDS WHERE THE USER IS CREATOR
Private Sub Form_Open(Cancel As Integer)
Dim Nom As String
Dim Prénom As String

Nom = DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") 'gets the Last name of the connected user and puts it in the variant Nom

Prénom = DLookup("[strPrénom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")'gets the First name of the connected user and puts it in the variant Prénom

DoCmd.ApplyFilter , "[strCréateur] = " & Nom & " " & Prénom'Displays a message if there is no reccords found
If Not FormHasData(Form) Then
MsgBox "Your did not create any of the boxes in the database" & Err.Description, vbExclamation, "Créateur inexistant"
End If

End Sub



I also tried things like :

'Home made starts with
DoCmd.ApplyFilter , "[strCréateur] = " & DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") & "*"

'No variant declaration
DoCmd.ApplyFilter , "[strCréateur] = " & DLookup("[strNom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'") & " " & DLookup("[strPrénom]", "tblUsers", "[strIGGID]= " & Environ("username") & "'")

And I tried to play with the "'" '" & "'" ="& = & &"... but didn't work


The weirdest part is that when I have this code in my form if I double click on the form in the database window nothing happens, not even an error message, I'm confused.

Thank you in advance for your suggestions.


Sponsored Links:



Hi,
I have column in table (imported from txt file) where two rows next to each other belong to same data like:
column1
row1 LD.1.TR.EUR.21050.1009.1700.Y .YU . YU.
row2 1.T.LONG.99

How can I concatenate or append lower row to row above to have it like :
LD.1.TR.EUR.21050.1009.1700.Y .YU . YU.1.T.LONG.99
Thanks




Hi,
I have column in table (imported from txt file) where two rows next to each other belong to same data like:
column1
row1 LD.1.TR.EUR.21050.1009.1700.Y .YU . YU.
row2 1.T.LONG.99

How can I concatenate or append lower row to row above to have it like :
row1: LD.1.TR.EUR.21050.1009.1700.Y .YU . YU.1.T.LONG.99
Thanks




I have created a Table Design
Name
Last Name
ID Number
NameID

I import data from CSV xls file which has Columns of Name, Last Name, ID Number.

What I want to achieve is while importing it should Concatenate Name and ID number and store in Name ID.

I have searched it well but havent been able to find solution. Any suggestion how to achieve this.


Thanks
NIRI




I have a table linked from DB1 in DB2 and I need to concatenate two fields in the table and update a field in another table in DB2.

I have this in the "update to" field but it isn't working.


	Code:
	"2011/12 COMPLETION DATE: " & [11-12 appraisals].[appraisal_completedate] & " - Comments" & [11-12
appraisals].[comments_currentyr]





I am trying to get the first, second, third, and fourth values into text box controls. Here's the steps I have so far:
-- I have a Top 4 query to return the top four records.
-- I run a Min query against those results to return the fourth record.
-- I use Dlookup in a text box control (Control Source property) to pull an individual field from the fourth record.

If I run the two queries in sequence with a specific criteria, the Dlookup returns the correct response.

I need to pass the criteria from the form into the Top 4 query, then again into the Min query, and finally into the Dlookup. And it's not working.

Is it possible to merge the two queries into one? "Get the Min of this Top 4"? I use Dlookup against a query successfully in another control; it's a pretty simple and straightforward query, though. This seems pretty complex.

Edit:
I tried putting the SQLs into a Function, but couldn't make that work, either. It looks like this:


	Code:
	 
 Public Function TopMinFLD4(meNum As Long) As Variant
Dim strSQL1 As String
Dim strSQL2 As String
strSQL1 = "SELECT TOP 4 tblEmplData.EmplNum, tblEmplData.LName, " & _
          "tblEmplData.EmplType, tblReviewPerf.ReviewName, " & _
          "tblReviewPerf.dtRevPerf, tblReviewPerf.numPointsPoss, " & _
          "tblReviewPerf.numPointsTotal, [numpointsposs]/[numpointstotal] AS [Avg], " & _
          "tblReviewPerf.TIRontime, tblReviewPerf.Reviewer " & _
          "FROM tblEmplData INNER JOIN (tblReviews INNER JOIN " & _
          "tblReviewPerf ON tblReviews.ReviewName = tblReviewPerf.ReviewName) ON " & _
          "tblEmplData.EmplNum = tblReviewPerf.EmplNum " & _
          "WHERE (((tblEmplData.EmplType)=" & Chr(34) & "DC" & Chr(34) & _
          " Or (tblEmplData.EmplType)=" & Chr(34) & "FL" & Chr(34) & ") AND " & _
          "((tblEmplData.EmplNum)=meNum) AND ((tblReviewPerf.ReviewName)=" & Chr(34) & _
          "FLD" & Chr(34) & ") AND ((tblReviewPerf.dtRevPerf) Is Not Null) AND " & _
          "((tblReviewPerf.numPointsPoss) Is Not Null) AND " & _
          "((tblEmplData.chkIsActive)=Yes)) " & _
          "ORDER BY tblReviewPerf.dtRevPerf DESC;"
DoCmd.RunSQL strSQL1
strSQL2 = "SELECT qryEmplRev_TOP_FLD_4.EmplNum, qryEmplRev_TOP_FLD_4.LName, " & _
          "qryEmplRev_TOP_FLD_4.EmplType, qryEmplRev_TOP_FLD_4.ReviewName, " & _
          "Min(qryEmplRev_TOP_FLD_4.dtRevPerf) AS MinOfdtRevPerf, " & _
          "Last(qryEmplRev_TOP_FLD_4.numPointsPoss) AS LastOfnumPointsPoss, " & _
          "Last(qryEmplRev_TOP_FLD_4.numPointsTotal) AS LastOfnumPointsTotal, " & _
          "Last(qryEmplRev_TOP_FLD_4.Avg) AS LastOfAvg, " & _
          "Last(qryEmplRev_TOP_FLD_4.TIRontime) AS LastOfTIRontime, " & _
          "Last(qryEmplRev_TOP_FLD_4.Reviewer) AS LastOfReviewer " & _
          "FROM qryEmplRev_TOP_FLD_4 " & _
          "GROUP BY qryEmplRev_TOP_FLD_4.EmplNum, qryEmplRev_TOP_FLD_4.LName, " & _
          "qryEmplRev_TOP_FLD_4.EmplType, qryEmplRev_TOP_FLD_4.ReviewName " & _
          "HAVING (((qryEmplRev_TOP_FLD_4.EmplNum)=meNum));"
DoCmd.RunSQL strSQL2
MsgBox "done!"
End Function

Any suggestions?
Ed




Actually have a two part question concerning my form and dlookup. Using Access 2007

I have a combo box for entering the name of the person placing an order. I have another field that looks for that person's email address (also have one for their phone number).

First question: how do I get rid of the #Error that shows on a new order form?

Second question: Can, and how, do I put this dlookup in VB rather than the Control Source? I'm thinking that would be faster, maybe not.

Form = ShirtOrders

NamesIDcbo Control Source = NamesID; Row Source = SELECT [qry_Names].[NamesID], [qry_Names].[FullName] FROM qry_Names ORDER BY [FullName];

Emaillkp Control Source =DLookUp("[Email]","qry_Names","[NamesID] =" & [Forms]![ShirtOrders]![NamesID])

I've looked in the forum and tried various other suggestions (Nz, IIf) to eliminate the #Error to no avail plus I've also tried some suggestions for putting the code to VB with the same result.

Keep in mind that I am a very basic user so any answers must be put in the simplest of terms (picture having to explain your answer to your pet gerbil!).

As always, thanks in advance for any suggestions.




I want to concatenate two fields together and display it in the text field, but I want the second text to be on the second line. What is the exact syntax to accomplish this in the Control Source. The concatenation part is easy, but what is the code to basically simulate ? I know I'm gonna feel stupid when someone responses




Hello,

I'm stuck on something, maybe you can help.

I have to concatenate the data in multiple records into one record. They have "skus" associated with them.

I have two columns.

ColumnSku: Which contains a product sku
ColumnModel: Which contains a model numbers

ColumnSku can contain the same sku hundreds of times
ColumnModel can contain the same model several times but not for the same sku

What I need to do is this
For every time a sku is shown in ColumnSku, take the model in ColumnModel and join them together separated by a comma.

For example

ColumnSku | ColumnModel
SKU1111 | Model11111
SKU1111 | Model22222
SKU1111 | Model33333
SKU1111 | Model44444
SKU1111 | Model55555
SKU9999 | ModelHHHHH
SKU9999 | ModelJJJJJ
SKU9999 | ModelMMMMM

Would end up like this
ColumnSku | ColumnModel
SKU1111 | Model11111,Model22222,Model33333,Model44444,Model5 5555
SKU9999 | ModelHHHHH,ModelJJJJJ,ModelMMMMM

How do I do this? Keep in mind too that I barely know update queries and have never used a Module (other than my failed attempt earlier today)

Please help if you can.
RB




I am trying to evaluate two IIfs in a report but have something wrong with the syntax. Here is what I have

=IIf([InvUM]="EA" And [InvRoLevel]




Hello all

My first post after 2 1/2 years reading the boards and obtaining a wealth of advice but this time I am stumped.

I have a subform that uses a Dlookup based on a date from the main form. This works fine every time, every record. (I have tried to use a query instead but this proved to be beyond my skills.)

The problem is that when I try to replicate this as a report / subreport, it works for the first record but returns a blank field for subsequent records.

Have I missed some basic fundamental fact about using Dlookup in a report rather than in a form?

Any advice is most appreciated.

Cheers
Rob




First this is the first thread to me in this board
I have form consist of subform and combo box .and I need to select one item from combo box to filter the data in the subform and the combo box contain data from table and subform contain data form query
and I need to use dlookup in setvalue in macro

PLEASE FIND ATTACH




I need to set two criteras in Query. When I do it now I write the following

"Pete" "Walt"

I get a syntax error




Hey,

I look everywere but i just cant find how to write two rows in single msgbox? Can someone pls tell me the code?

THX




Hello
I have a one field in the form A and i would like to update two fields in the subform B.
I have in both forms an ID thats equal.
I programmed and everything looks good on the first record but it doesn't update the second record.
I've tryed Nextrecord, recordset... but i believe i'm not puting them in the right spot.
Help please
Pedro




I've got two queries in Access, one displays all students who got A's or B's for a subject (query A) the other displays students who didn't. (query B)

Now I'm stuck as to how to get a table which displays only the students who appear in query A but not in query B.

The problem is that the each student can have multiple records in each of the query results tables as they all have a record for every subject they take.

Can anyone give me any idea as to how to sort this out please? I'm guessing VB is the only way but its been ages since I programmed anything. Or is there away of doing the comparison using Queries.

Please help
Melissa




Hi
Just joined this forum looking for some help on a small ( 11Meg) Access database.
I do not have a SQL server background
I work in a larger organization where we have a DBA group but they do not support the migration from Access to SQL server. They support the actual database only No query support on a migration.

I have just reworked an Access database to provide additional functionality. We need to share the data Nationally so we want to backend the database on a SQL server to provide all the benefits of SQL server. I have already run through the simple queries and made them Passthrough queries.
The issue I have is I inherited about 15 queries with the same Dlookup in each of them
The Access forms actually have embedded DLookups as well
Looking for a solution that would allow a more cut and paste fix as the Dlookups are all the same
Here is an example of one of the queries:

SELECT SerializedInventory.InventoryStatus, SerializedInventory.UsageStatus, SerializedInventory.InventoryType, SerializedInventory.Manufacturer, SerializedInventory.ModelName, SerializedInventory.SerialNumber, SerializedInventory.PinNumber, SerializedInventory.MACAddress, SerializedInventory.EndofLife, SerializedInventory.Group, InventoryType.Type
FROM InventoryType INNER JOIN SerializedInventory ON InventoryType.TypeID = SerializedInventory.InventoryType
WHERE (((SerializedInventory.InventoryStatus)="undetermi ned" Or (SerializedInventory.InventoryStatus)="current inventory") AND ((SerializedInventory.EndofLife)=0) AND ((SerializedInventory.Group)=DLookUp("[defswitchboard]","tblDefaults")));

I have already found the downfall of the DLookup's in that they create a large amount of traffic between the workstation and server
Would really like to get them out of the code as passing the data across the country really slows down the interface response

Thanks
RRII




Hi,
How to Add More than two number in same control

For Example:
I m having one TextBox Control I have to add three values in same Control and display result into other Control

i.e TEXTBOX one= 13+13-1
display in
TEXTBOX TWO=25
I Have add first two numbers and subtract it from another using only one text box control and out have to display in second textbox control.
Pls can any one can gve idea.

With Regards,

Umair




Hi all,

This may be dump question. Don't laugh at me..Can I display the data from two tables in a report without using a query. Why I need this because I had two tables which has no common fields in that... Or if there is a way how can I do that....


thanks in advance

Thanks




Hi!!
I have a table too long and I would like to make two pages in the form and I dont know how to do that.
I dont have much experience.
Is that posible??
Thank you!