Another DLookup problem with 3 criteria


I searched the forum for this problem but my problem is still unique.

Problemsituation (see attachment):
I have a form (frmAanvoergegevens) with 3 fields:

* Contractnummer - txtContractnummer - textbox - numerical
* Productcode - cboProductcode - combo box - text
* Leverancierscode - cboLeverancierscode - combo box - text

I also use a table called tblContracten. The fields above are also in that table.

If I click on the button 'Opslaan' I want to check if the fields (which are filled in) are the same as the fields in the table tblContracten. If yes, then a message will pop-up.

Until so far I used the following code:

	If Not IsNull(DLookup("[txtContractnummer]", _
    "tblContracten", "[Contractnummer] = " & Me.txtContractnummer _
    & " And [Productcode] = '" & Me.cboProductcode _
    & "'" And "[Leverancierscode] = '" & Me.cboLeverancierscode & "'")) Then
    MsgBox "The contract is ok.", vbExclamation
    End If

I need some help...


Post your answer or comment

comments powered by Disqus
Is there a way to do a DLookup with 3 criteria, I have tried this with no luck...

=DLookUp("[Price]","tblPrice","[LocationID] = " & [Location] & " And [Date]=#" & [TodaysDate] & "#" And "[MonthForward] = #01/01/2004#")


Problem with a DLookUp.
I am currently working in MS Access 2007 and I attached the following code to a unbound text box which is located on a tab control

=DLookUp("[ProdSpecAdr]","t_Suppliers","[pk_SUP_id] =" & [Forms]![f_DE_CPN]![fk_SUP_id])

Everything was working great till I placed this form onto another form making it a Subform... and now my unbound text box is displaying the following......#Name?

Could use some help I've been looking for a fix for awhile now, thanks in advance.


I am currently working on an Access 2007 form. This form will be used for taking orders. It then will be printed or saved as PDF. The form gets its information from queries, that are based on live links with few excell spreadsheets. The criteria for the queries are set by the fields on a form. For example, after selecting the customer, one query will search for all avaiable products for that customer. After selecting a product, another query gets the Pack size. To get the "SP - £/kg" and "BP - £/kg" I used a query that uses "Product", "Customer" and "Total (amount of product)" as its criteria.

My problem is with textboxes for "SP - £/kg" and "BP - £/kg". They all use Dlookup function to get their values from the query - It works fine.
However if, lets say, only 1 product is selected on the form, the other 5 rows stay unpopulated. This makes the other 5 "SP - £/kg" and "BP - £/kg" rows stay empty. Unfortnately, this meses up calculations for "Profit" and "Total Selling Price" I managed to identify the problem, that is If I use empty textboxes in calculations, I just get empty fields for results.

My question is, is it possible to make the "SP - £/kg" and "BP - £/kg" fields to be 0 (or at least used as 0 in calculations) instead of being empty? I tried setting the default value to 0, that didn't work. I attempted to use Iff function but I failed miserably. I couldn't find anything helpful on the internet neither. The text boxes are set to 'currency' format. Perhaps there is a solution in the calculations?

If everything else fails, I will just use List Boxes instead of textboxes with Dlookup. Problem with list boxes is that you have to first select the value (note there is only 1 to select), I want to avoid that.

Any help will be greatly appreciated !


I want to use a Dlookup() function with multi criteria of two Numeric fields in Access 2010. The function give me result when I use them individually. When I combine both criterias, it is not filtering records to t combined criteria. Can anybody tell me what I am doing wrong here? The following statement I use as the control source of that field:

IIf([ToolNumber]=0,"N/A", DLookUp("[bslocation]","[ballscoop]","[noofballs] =" & [Forms]![ASSEMBLYTOOLS]![ToolNumber] And "[ballsize] =" & [Forms]![ASSEMBLYTOOLS]![bsballsize]))

Fields [noofballs], [bsballsize] and [ToolNumber] are numeric

Second Question:

In a tabl, I want to use a field to enter data like 1/16, 3/32, 1/8, 5/16, 1/4 etc etc... and do calculations with that field, what format I should choose?. When I choose the field as number type, it converts the value to decimals. Can I use Text format and do calculations with those fields? Or is there any special type available in Access 2010 to accomodate such formats?

Thank you and best regards

I have a set query that displays items that are in a certain status. Its more like a SQL server view if you're familiar with that. My back end is a SQL server 2005, and Access is my front end.

Here's my problem. So, the items that display in this query are those that meet certain criteria. For example, the control [TBB_NB] (Yes/No datatype) has to be set to "Yes"; [Built_on_NB](also a Yes/No datatype) must be "No"; and one other control has to be set to Null. If an item meets these criteria, then it displays in the query. Really child's play stuff, right?

This query and those like it have worked flawlessly for years in my DB. Now, one of my queries (and possibly more, since its a little hard to figure out which items aren't working) isn't displaying the items correctly. For example, I currently have about 6 items in my DB that meet the above specified criteria, but they are NOT showing up on the query. If I toggle the Yes/No fields back and forth and then re-query then it will display the items correctly.....Its almost like the data in the "Yes/No" fields isn't registering properly. All Yes/No fields default to "No", so I know its not a problem with the field being Null and I'm asking for either a "Yes" or a "No"

What's worse, is it appears to be totally at random, so its really difficult to troubleshoot.

Anyone have ANY ideas?

I have a crosstab query that has the error: The Microsoft Jet engine database does not recognize [Forms]![frmMasterYear]![frmYear] as a valid field or expression.

I have a query called C/Y companies which displays the year and company fields from one of my tables, with the criteria in the year field being [Forms]![frmMasterYear]![frmYear]. Basically, this shows all companys that have a year field in the database equal to what has been selected in the form. In a seperate query (called GeographicFinanicalData) I have joined the company name in the C/Y Companies query to the table which I am getting my fields from as to only show results for companies who have a record with the year matching what is selected in the form. I then use a crosstab query to get summary data from the GeographicFinancialData query. All of the other queries work as a standalone process, however the crosstab query gives the error mentioned above. The crosstab query will work if I change the criteria in the C/Y companies table to something like (SELECT max([Year]) from [4 Premium Analysis]) - I originally had this criteria but I want to change to allow the user to be able to control what year is used as the criteria.

Any suggestions on how to get this to work would be greatly appreciated.

I am trying to open a report base on 3 criteria as follows:
DoCmd.OpenReport "rpt_sumReadmissionDate", acViewPreview, , ("[Source]' Non-Collaborative Facility' And [CurrentAdmit]-[DischargeDate]>2 And [Home]'Yes'")

The first two criteria work fine but when I added the third criteria hundreds of records dropped form the report although majority of the records in the database meet all three criteria.

I'm not sure what I am doing wrong of if access allows for 3 criteria. PLEASE HELP

Ok, so I'm having some trouble with a DLookUp function which is driving me insane. I have an input form which is based on 2 queries. The queries are called [Learner Details Query] and [Aims Query]. The tables are joined so that each learner record in the [tblLearnerDetails] can have multiple aim records in the [tblAims] table.

There is another table which lists window numbers with corresponding window start and end dates called [tblPSCNQNWindowNumbers].

I am trying to use a DLookUp to find for each record in the in the [tblAims] table the corresponding value in the [fldPSCNQNWindowNumber] field on the [tblPSCNQNWindowNumbers] table when the [Planned End Date] field on [tblAims] falls between the [fldPSCNQNWindowStartDate] and [fldPSCNQNWindowEndDate] on the [tblPSCNQNWindowNumbers] table.

Now I know this straight forward DLookUp example is everywhere and I shouldn't need to start a new thread. However, this problem gets a bit complicated down the line. So, at the moment I've got:

1. Attempt using "BETWEEN" and "AND" operators:

	DLookup("[fldPSCNQNWindowNumber]" , "[tblPSCNQNWindowNumbers]" , "[Planned End Date] BETWEEN #" &
[tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" )

2. Attempt using "=" operators:

	DLookUp("[fldPSCNQNWindowNumber]" , "tblPSCNQNWindowNumbers" , "[fldPSCNQNWindowStartDate]  #" & tblAims.[Planned End Date]
& "#" )

So neither of these approaches seem to work. I did have this step of the problem solved in the past but my old machine died and I didn't keep my notes after solving the problem once already (stupid I know).

3. Anyway, another step of the problem is that somtimes there are NULL values for [Planned End Date] so I remember last time I had to wrap the DlookUp in an IIF(ISNULL()) function. Giving me something like this:

	IIF(ISNULL([Planned End Date] , "" , (DLookUp("[fldPSCNQNWindowNumber]" , "[tblPSCNQNWindowNumbers]" , "[Planned End Date]
BETWEEN #" & [tblPSCNQNWindowNumbers]![fldPSCNQNWindowStartDate] & "# AND #" &
[tblPSCNQNWindowNumbers]![fldPSCNQNWindowEndDate] & "#" ))))

So after doing some reading I also found out that SQL doesn't like the UK date format (dd/mm/yyyy) and that I should use a function to change the format of the date fields to US/ISO/ANSI. Fitting the following snippet somewhere into my code:


4. Giving something like this:

	IIF(ISNULL([Planned End Date] , "" , DLookup("[fldPSCNQNWindowNumber]","[tblPSCNQNWindowNumbers]","[Planned End Date]
Between "  & Format([fldPSCNQNWindowStartDate],"yyyy-mm-dd") & " And " & Format([fldPSCNQNWindowEndDate],"yyyy-mm-dd"))))

5. Of course, I'm getting way ahead of myself as I haven't got the first little problem sorted yet. Bear with me. Now I have a realisation, as I'm trying to put all this together as the calculated field [Window Number] in the [Aims Query] subquery using the expression builder. I want to use the result of the calculated [Window Number] along with [Area] in a further DLookup to find the values of the field [PSCNumber]. This is as far as my progress got last time. I figure a calculated field can't use the result of a calculated field within the same query? Now I know that it's a bad idea to put calculated fields into tables but I was thinking that I should create the [Window Number] and [PSCNumber] fields in the [tblAims] table and run the calculations as after update event procedures in order to seperate the functions from the same query. Isn't the syntax different in the VBA code builder? This idea seems a messy workaround. Can someone help me with my code along the steps outlined and direct me towards the correct solution to this calculated fields on a query problem?

Thanks in advance, Mike.

This forum is incredibly useful! Particularly for individuals who are not experts in MS Access or programming. Any assistance would be GREATLY appreciated!

Have another problem with a training scheduling database, with the auto generation of emails to participants. They use MS Office 2003. Three problems:

1) They would like the introduction text to be bold and in red text. Have tried several things based on research I could find, but I can't seem to get it to work. The fields in the code would be called "stInviteTextIntroEnglish" and "stInviteTextIntroFrench".

2) When creating the body text of the email, there's some headers such as "When & Where:" that also needed to be bolded and underlined and in blue text.

3) Right now the code will only send one email to one participant. Need to find a way to loop and store all of the participants emails and save them to create only one email.

The code right now works to generate a basic email, now have to work on the final steps.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim db As DAO.Database
Dim MyOutlook As Outlook.Application
Dim varTo As String '-- Address for SendObject
Dim Emailsingle As String '-- initial emails
Dim stCourseDesc As String '--- course description
Dim stCourseDescFrench As String '--- course description french
Dim stPrerequiste As String '-- prerequesites
Dim stPrerequisteFrench As String '-- prerequesites french
Dim stSubject As String '-- Subject line of e-mail
Dim stStartDate As String '-- The start date of the course
Dim stStartDateFrench As String '--- The start date of the course in French
Dim stStartTime As String '-- The start time of the course
Dim stLocation As String '-- the facility
Dim stLocationFrench As String '-- the facility in French
Dim stCourseName As String '-- the course name
Dim stCourseNameFrench As String '--- The course name in french
Dim stInviteTextIntroEnglish As String '--- the intro message
Dim stInviteTextIntroFrench As String '--- the intro message in French
Dim stInviteTextFewMoreThings As String '-- the end message
Dim stInviteTextFewMoreThingsFrench As String '-- the end message
Dim StTextEnglish As String '--- the body text
Dim StTextFrench As String '---- the body text in french

Dim MyMail As Outlook.MailItem
Dim fso As FileSystemObject
Set fso = New FileSystemObject
varTo = DLookup("[EmailAddresses]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseDesc = DLookup("[CourseDescription]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseDescFrench = DLookup("[CourseDescriptionFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stPrerequiste = DLookup("[Prerequisites]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stPrerequisteFrench = DLookup("[PrerequisitesFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stStartDate = Format(DLookup("[StartDate]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]"), "mmmm dd, yyyy")
stStartDateFrench = DLookup("[FrenchDate]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stStartTime = Format(DLookup("[StartTime]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]"), "hh:mm")
stCourseName = DLookup("[CourseName]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stCourseNameFrench = DLookup("[CourseNameFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextIntroEnglish = DLookup("[InviteTextIntroEnglish]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextIntroFrench = DLookup("[InviteTextIntroFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextFewMoreThings = DLookup("[InviteTextFewMoreThings]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stInviteTextFewMoreThingsFrench = DLookup("[InviteTextFewMoreThingsFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stLocation = DLookup("[FacilityName]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityAddress]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityCity]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[Province]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")
stLocationFrench = DLookup("[FacilityNameFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityAddressFrench]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[FacilityCity]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]") & ", " & _
DLookup("[Province]", "qry_Invite_email", "[ParticipantsID] = Form![ParticipantsID]")

stSubject = "Cours/Course: " & stCourseNameFrench & " / " & stCourseName
StTextFrench = stInviteTextIntroFrench & Chr$(13) & _
"Invitation à assister à la séance : " & stCourseNameFrench & Chr$(13) & _
Chr$(13) & _
stCourseDescFrench & Chr$(13) & _
Chr$(13) & _
stPrerequisteFrench & Chr$(13) & _
Chr$(13) & _
"Date et endroit:" & Chr$(13) & _
"Date: " & stStartDateFrench & Chr$(13) & _
"Heure: " & stStartTime & Chr$(13) & _
"Endroit: " & stLocationFrench & Chr$(13) & _
Chr$(13) & _
"Renseignements supplémentaires:" & Chr$(13) & _
stInviteTextFewMoreThingsFrench & Chr$(13) & _
StTextEnglish = Chr$(13) & _
stInviteTextIntroEnglish & Chr$(13) & _
"Invitation to attend the course: " & " " & stCourseName & Chr$(13) & _
Chr$(13) & _
stCourseDesc & Chr$(13) & _
Chr$(13) & _
stPrerequiste & Chr$(13) & _
Chr$(13) & _
"When & Where:" & Chr$(13) & _
"Date: " & stStartDate & Chr$(13) & _
"Time: " & stStartTime & Chr$(13) & _
"Location: " & stLocation & Chr$(13) & _
Chr$(13) & _
"And a few more things:" & Chr$(13) & _
stInviteTextFewMoreThings & Chr$(13) & _

Set MyOutlook = New Outlook.Application
'Write the e-mail content for sending to assignee

Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.BodyFormat = olFormatRichText
MyMail.To = varTo
MyMail.Subject = stSubject
MyMail.Body = StTextFrench & StTextEnglish


Me.InviteSent = Date
Set MyMail = Nothing
Exit Sub
MsgBox "You are missing information to complete this email (email address, course info, facility info)"
Resume Exit_Command24_Click

Hi Everyone -

I have a problem when I try to use a sequence field in another query.

I have a query called qryIncidentDropOffDates. In this query I have created a field called "Sequence" with the following:

(SELECT Count(*) FROM qryIncidents AS T WHERE T.SVNumber = qryIncidents.SVNumber AND T.IncidentDate


I have a database with two tables structured as in attachement. In a nutshell I have a list of values with two criteria associated in one table and a second table with a 3-rd criteria. What I need is to have a query with a list of values (corresponding to Criteria 1=yes) if Criteria 3 is on "Yes", and with another list of values (corresponding to Crirteria 2=yes) if Criteria 3 is on "No". Can this be solved only in one query? Please advice. Thank you.


Can someone help me with this please.

I have 3 fields in a table - Pupil ID, Date and Price.

I have criteria on the date field - between x and x date.

What I would like is the sum of the price broken down by Pupil ID between the inputted dates.

I have set the Grouping property of Price to SUM and the Pupil ID to Group By. I have disabled the 'Show' on the date field so the query only needs to output 2 fields.

The problem I am getting is that is doesnt seem to group the pupils. I get 2 rows with the same pupil (I have 2 records with 2 different prices for that pupil). If i remove the date field and criteria - it works fine but doesnt give me what I need.

Please can someone help me with this?

Many Thanks

hello and heres yet another dlookup question, its annoying me and ive tried a few different variations.

its a dlookup with 2 criteria, i cant seem to resolve why i keep getting data type mismatch error,

here my bit of code

If Counter = DCount("[Alias]", "[tblAlias]", "[Alias]='" & sNick & "' And [UserID]='" & sID & "'") > 0 Then

no doubt some1 will solve it within 2secs , im positive the problem is to do with the sID which holds a userid, i know its prefixed with a s but its actualy a double not a string

any help much appreciated


I have the following code in the on click event of a commad button
I am looking to duplicate the majority of the last record using this command button(further similar lines of code not shown). The problem I am encountering is that the Field SalesID is an autonumber field and is at 23650 as a record count and the record this bit of code returns is record 110. Can someone tell me where I am going wrong with the criteria
I have tried taking out the Nz portion and also used DLast, but to no effect.

Me.ProjectID = DLookup("[ProjectID]", "tblSalesDetails", Nz(DMax("[SalesID]", "tblSalesDetails"), 0))

Many thanks in advance


I have posted a couple of times about a loop I am trying to create to populate Outlook appointments, the loop is working but my record selection is not.
I have posted the whole code below, but have highlighted the bits that are causing problems
The purpose of the code is to create an appointment on Outlook with various bits of info from the Job creation for, for each person allocated to the job. My problem is that my code selects the number of relevant records, but when I try to specify employees name using dlookup I am struggling with the Criteria to tie it up to the records I have selected.

All comments are welcome

Private Sub btnAddApptToOutlook_Click()
'On Error GoTo ErrHandle

Dim olNS As Object
Dim olApptFldr As Object
Dim startid As String
Dim endid As String
Dim firstsubjob As String
Dim lastsubjob As String

startid = 1
endid = DCount("Jobnumber", "tblemployeesonjob", "jobnumber=" & Me.JobNumber)
firstsubjob = 1
lastsubjob = DCount("RemJobNo", "tblassistenvsubjobs", "remjobno=" & Me.JobNumber)

' Save the Current Record
If Me.Dirty Then Me.Dirty = False

' Exit the procedure if appointment has been added to Outlook.
If Me.ApptAdded = True Then
MsgBox "This appointment has already been added to Microsoft Outlook.", vbCritical
Exit Sub

' Use late binding to avoid the "Reference" issue
Dim olApp As Object 'Outlook.Application
Dim olAppt As Object 'olAppointmentItem

'This is how we would do it if we were using "early binding":
' Dim olApp As Outlook.Application
' Dim olappt As Outlook.AppointmentItem
' Set olapp = CreateObject("Outlook.Application")
' Set olappt = olapp.CreateItem(olAppointmentItem)

If IsAppThere("Outlook.Application") = False Then
' Outlook is not open, create a new instance
Set olApp = CreateObject("Outlook.Application")
' Outlook is already open--use this method
Set olApp = GetObject(, "Outlook.Application")
End If
For a_counter = startid To endid
Set olAppt = olApp.CreateItem(1) ' 1 = olAppointmentItem

' Add the Form data to the Appointment Properties
With olAppt
If Nz(Me.chkalldayevent) = True Then
.Alldayevent = True

' Format the dates in the Form Controls
Me.txtStartDate = FormatDateTime(Me.txtStartDate, vbShortDate)
Me.txtEndDate = FormatDateTime(Me.txtEndDate, vbShortDate)
' For all day events use "" for the start time and the end time
Me.cboStartTime = ""
Me.cboEndTime = ""

' Get the Start and the End Dates
Dim dteTempEnd As Date
Dim dteStartDate As Date
Dim dteEndDate As Date
dteStartDate = CDate(FormatDateTime(Me.txtStartDate, vbShortDate)) ' Begining Date of appointment
dteTempEnd = CDate(FormatDateTime(Me.txtEndDate, vbShortDate)) ' Use to compute End Date of appointment

' Add one day to dteEndDate so Outlook will set the number of days correctly
dteEndDate = DateSerial(Year(dteTempEnd + 1), Month(dteTempEnd + 1), Day(dteTempEnd + 1))

.Start = dteStartDate
.End = dteEndDate

' Set the number of minutes for each day in the AllDayEvent Appointment
Dim lngMinutes As Long

lngMinutes = CDate(Nz(dteEndDate)) - CDate(Nz(dteStartDate))
' The duration in Minutes, 1440 per day
lngMinutes = lngMinutes * 1440

' Add the minutes to the Access Form
Me.txtApptLength.Value = lngMinutes

.Duration = lngMinutes


' The Validation Rule for the Start Date TextBox requires a
' Start Date so there is no need to check for it here
If Len(Me.cboStartTime & vbNullString) = 0 Then
' There is no end time on the Form
' Add vbNullString ("") to avoid an error
Me.cboStartTime = vbNullString
End If

' Set the Start Property Value
.Start = FormatDateTime(Me.txtStartDate, vbShortDate) _
& " " & FormatDateTime(Me.cboStartTime, vbShortTime)

' If there is no End Date on the Form just skip it
If Len(Me.txtEndDate & vbNullString) > 0 Then
If Len(Me.cboEndTime & vbNullString) = 0 Then
' There is no end time on the Form
' Add vbNullString ("") to avoid an error
Me.cboEndTime = vbNullString
' Set the End Property Value
.End = FormatDateTime(Me.txtEndDate, vbShortDate) _
& " " & FormatDateTime(Me.cboEndTime, vbShortTime)
End If
End If

If Len(Me.txtApptLength & vbNullString) = 0 Then
Dim timStartTime As Date
Dim timEndTime As Date

' Format the Start Time and End Time
timStartTime = FormatDateTime(Me.txtStartDate, vbShortDate) _
& " " & FormatDateTime(Me.cboStartTime, vbShortTime)
timEndTime = FormatDateTime(Me.txtEndDate, vbShortDate) _
& " " & FormatDateTime(Me.cboEndTime, vbShortTime)

.Duration = Me.txtApptLength
End If
End If

If Nz(Me.chkalldayevent) = False Then
.Alldayevent = False
End If

If Len(Me.SurveyorNo.Column(1) & Me.worktype & vbNullString) > 0 Then
.Subject = Me.EnqNumber & "---" & Me.JobNumber & "---" & DLookup("employeename", "tblemployeesonjob") & "---" & Me.worktype
End If

If Len(Me.JobNumber & vbNullString) > 0 Then
.Body = Me.JobNumber
End If

If Len(Me.siteref.Column(2) & vbNullString) > 0 Then
.Location = Me.Client.Column(0) & "---" & Me.siteref.Column(2)
End If

.ReminderSet = False

' Save the Appointment Item Properties

End With
Next a_counter
' Set chkAddedToOutlook to checked
Me.chkAddedToOutlook = True

' Save the Current Record because we checked chkAddedToOutlook
If Me.Dirty Then Me.Dirty = False

' Inform the user
MsgBox "New Outlook Appointment Has Been Added!", vbInformation
End If

' Release Memory
Set olApptFldr = Nothing
Set olNS = Nothing
Set olAppt = Nothing
Set olApp = Nothing
Exit Sub

MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
& vbCrLf & "In procedure btnAddApptToOutlook_Click in Module Module1"
Resume ExitHere

End Sub

Many thanks



I have a problem with Dao.recordset. My code looks like this:

Set db= CurrentDb
Set rst = db.OpenRecordset("select from tblBooks where = '" & IDBook & "'")

IDBook is an integer within a form. I think there is a problem with syntax, but I can't figure it out. I tried putting ";" at the end of criteria but nothing.

And another question: when writing a query how do I reference criteria with variables in a form? I use forms![frmName]![name]. It works only with objects (text box, list box,...).

Thanks in advance!


I shall try and explain this problem in a far less vague way than the title suggests!

1 table with 3 fields. MemberNo/date/paid
4000,08/01/2004,True etc...

What I'm trying to do is sort firstly by [MemberNo], then by [paid]. If ALL and ONLY ALL of a persons records are true then I want to delete all their records from the table, (or append them to another table but delete will do here).

If for instance [MemberNo] = 4000 and his first 9 records are true but the 10th is false then ALL of his records need to stay in the table, i.e. do nothing with them and move on to the next [MemberNo]

Thus I want to go thru the whole table, finding MemberNo's where ALL their records in the paid field are the same and then 'do something' with the records. The 'do something' appears to be the easy bit!

Simple multiple queries don't appear to be the answer but feel free to set me straight if I've just not stuck at it long enough...

I've tried doing this in code and so far managed not much at all. I'll no doubt have it down by the end of the weekend but if I spend the next two days coding my wife may attempt to kill me. Please take pitty! Any suggestions, aside from "get divorced" would be much appreciated!

Thank you...

Evening all.

I'm having a very frustrating problem with some Excel arrays, and as this forum has solved all my Access problems to date, I thought I'd turn to you now.

The purpose of the model is to take accruals data submitted in a spreadsheet, look up various fields from another source, and output the results to a template for upload into the GL system.

I have two tables in two separate excel workbooks. The source table has been read into Temp() dynamic array, and the lookup table has been read into TUCostCentreList() dynamic array.
Variables Prop and CCtr are declared as variants.

Prop is the property number as held in the third column of the Temp array. To ensure that I'm comparing like with like, I've converted it to string format :
Prop = CStr(Temp(i, 3)) - i is the value for loopng through the Temp array

I then look up the property number in the Cost Centre array, where the property number is in column 7, and the Cost Centre is in column 5 :
CCtr = TUCostCentreList(Application.Match(Prop, Application.Index(TUCostCentreList, 0, 7), 0), 5)

There is an error handler to write the line number i and the property number if not found.

My problem is that sometimes, the lookup line produces a "Type mismatch" error. This does not happen every time the lookup fails - that would make more sense! As it loops through the Temp array, some of the errors are captured in the error handling routine, and some cause the break error. There doesn't seem to be any correlation between the lines that cause the error, only that if I move the suspect lines around, they always cause the error

Any thoughts anyone has that may help would be gratefully appreciated.

Many thanks in anticipation


Hi All,

I am having a problem with a form that I have which has a query as its recordsource.

The problem: I have a search form (frmSearch) that lists records in a list box on the form. The unique ID for the records in the listbox is the PositionID. I also have another form (frmTracking) that has a query as its recordsource. The query uses this code in the queries Criteria to filter the record returned to that of the one selected in the frmSearch listbox: Forms!frmSearch!List0

SO basically, the user chooses a record in the frmSearch listbox, double-clicks that record which then (on the double-click event) opens frmTracking with the correct record. Simple enough... However...

THe problem I am running into is that not all of the PositionID's displayed in the listbox will already have a tracking record. The ones that do not I want to have the form open to a new record and transfer the PositionID over to the frmTracking. Currently, the form opens blank (no textboxes or nothing) because the WHERE criteria is null and because there was no positionID found in the tracking table so there is nothing to display.

Does anyone know a way to get the form to recognize there is not record established in the tracking table and create a new one for the Position ID selected in the listbox?

Any/All help greatly appreciated!

I have a aPointsRate table such as shown below:


When a user enters a Date and TimeIn, eg:
Date=21/10/2003, TimeIn=3:00pm
I expect to retrieve MultiplyBy=1.0

My DLookup looks like this:
=DLookUp("[MultiplyBy]","aPointsRate",("[DayTypeID]= " & Weekday(Forms!ufmService!ufmServiceVisit.Form!Date )) And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceD etails.Form!TimeIn & " >= [BeginTime]") And (Forms!ufmService!ufmServiceVisit.Form!ufmServiceD etails.Form!TimeIn & "

I have two databases running for a small company. they are identical in design, but they wanted them kept as separate databases. Over the last 3 or 4 weeks, they have been getting an error that says 'you canceled the previous action' they have to close the dB down then the one screen refuses to open. Now they admit to initially getting the message 'this computer does not have enough memory to perform this action'. It seems to happen after they have done a 'filter by form' updated data on the subform, then filtered out.

I have done a compact on the database, set new macros so that only one form can be open at a time with a 'save' added for safety.

I have moved the whole database to another pc and they now access it from there and the problem still occurs. I wondered if they were making a mistake and having done the 'filter by form' instead of using 'remove filter/sort' they were re selecting filter by form, but i cant tell as I am not there whn they are working.

The other database works fine, and has shown none of the errors. Is it possible there is a problem with the pc itself, it shows it has plenty of free space, but i dont know what the cache memory is.

HAs anyone got any ideas? The only way i can solve the problem when it occurs is to re-import the affected form, and it then works perfectly again.

Ok here is the problem I have, I need to have a way for an end user to select infomation on a form that is pulled from a table and displayed in a way that they can select more than one item (I am assumeing a list box will be best) then write those multiple values to another field(s) in another table.

Here are some of the problems with that:
1. The user is defineing the amount of possible selections
2. The user can select anywhere from 1 to any number of selections from that list box.

In case I confused you on what I am trying to accomplish let me give an example.

User1: fills out form 1 with 5 different records, which get written to table 1.

he then goes into form 2, form 2 has a list box displaying the 5 records from form 1, he selects 3 of those use the click and ctrl key method.

He then saves that record and Access writes all the data collected in form 2 as a record in table 2.

Any help would be appreciated.

At first i want to apologize for my bad english.

I have something problems with registrations, in my BD. I have 3 tables.

Clients, Movies and Bills. I enter 5 registrations for movies, i delete them, and when i try to enter another registration, that appers with no. 6. (Type of data for Fieldname CodeMovie, is autonumber).

Second problem is that i want to display a bill. All of fieldname of Bills Table displayed like a bill, or almost a bill.

Someone can help me? i attache my BD but, it is in french. I hope so that is not a big problem.

P.S. i hope i post in the right place

Hi everyone,

I need help with one very simply update query which I need to run.
I have made a simple update query with one criteria:
It works great.

UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SET tblArticle.PriceROZCoef = 4
WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));

Now my next step is to create query where i have mulltiple creteria for example:
UPDATE tblArticle INNER JOIN qrsLimPriceNEW ON tblArticle.ArticleID = qrsLimPriceNEW.ArticleID SETtblArticle.PriceROZCoef = 2 WHERE (([qrsLimPriceNEW].[Expr2] Between 0.61 And 1.50));
tblArticle.PriceROZCoef = 3 WHERE (([qrsLimPriceNEW].[Expr2] Between 1.51 And 2.2));
tblArticle.PriceROZCoef = 4 WHERE (([qrsLimPriceNEW].[Expr2] Between 2.21 And 2.95));
All together I have 7 different conditions.

I simply don't know what funcition I need to use to make it work in one query together not 7.

I tried searching there some similar problems but I still don't understand.
Thanks for help in advance.

Not finding an answer? Try a Google search.