DLookup Function

[irenef1223]

Hello! I need some help on the DLookup function in access. I have a form with an unbound textbox and a button, and what I want the button to do is, based on the number of days entered in the unbound textbox, open a form that will allow me to send emails to recipients based on the notification dates in my extension table. Below is the code I'm using; however, when I enter the number of days I only receive the message that I do not have notices to send, but there are notices that need to be sent, so my code isn't looking up the dates correctly. The criteria in my query for each of the notification dates is <(Date()+[Forms]![EXT NOTICE]![txtNotify])

Button Event Procedure:

Private Sub Command2_Click()
Dim db As DAO.Database, rst As DAO.Recordset
If IsNull(DLookup("[EXT NOTIFY DATE 1] &','& [EXT NOTIFY DATE 2] &','& [EXT NOTIFY DATE 3] &','& [EXT NOTIFY DATE 4]", "[EXT TBL]")) Then
MsgBox "There are no extension notices to send."
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "MAIN MENU", acNormal, "", "", acAdd, acNormal
Else
' Point to this database
Set db = CurrentDb
' Clean out the working table
CurrentDb.Execute "DELETE * FROM [EXT TBL]", dbFailOnError
' Avoid prompts
DoCmd.SetWarnings False
' Load in the rows to select
DoCmd.OpenQuery "EXT NOTICE Query"
' Turn on prompts again
DoCmd.SetWarnings True
' Clear the database object
Set db = Nothing
' Open the selection form
DoCmd.OpenForm "EXT SEND FRM"
' Close me
DoCmd.Close acForm, Me.Name
End If
End Sub




Any help is greatly appreciated!

[ranman256]

sWhereClause = "[name]='Bob'"

=Dlookup("[Field2Return]", "qryOrTbl",sWhereClause)

[Welshgasman]

Does no-one use Google?
https://support.microsoft.com/en-us/...b-bed10dca5937

[irenef1223]

Isn't anyone kind or understanding anymore? I thought this was a forum where you could come and get help, or am I wrong? I DID Google, and none of the sites, including the one you provided, helped me with my issue. I'll try to find another site where perhaps people are more willing to help than put others down or make them feel dumb for asking for help. Have a great day!

[davegri]

The DLookup worked for me, but you're clearing your table before running the query, so nothing will ever be found.
You don't need to dim the db or the rst, as they aren't used.

Option Compare Database
Option Explicit


Private Sub Command2_Click()
    If IsNull(DLookup("[EXT NOTIFY DATE 1] &','& [EXT NOTIFY DATE 2] &','& [EXT NOTIFY DATE 3] &','& [EXT NOTIFY DATE 4]", "[EXT TBL]")) Then
        MsgBox "There are no extension notices to send."
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "MAIN MENU", acNormal, "", "", acAdd, acNormal
        DoCmd.Close acForm, Me.Name
        Exit Sub
    End If


    ' Clean out the working table
    CurrentDb.Execute "DELETE * FROM [EXT TBL]", dbFailOnError  '<<<<<<<<<<<<<<<<
    ' Avoid prompts
    DoCmd.SetWarnings False
    ' Load in the rows to select
    DoCmd.OpenQuery "EXT NOTICE Query"            '<<<<<  looks at table [EXT TBL] ?  It's empty!
    ' Turn on prompts again
    DoCmd.SetWarnings True
    ' Open the selection form
    DoCmd.OpenForm "EXT SEND FRM"
    ' Close me
    DoCmd.Close acForm, Me.Name
End Sub

[mike60smart]

Hi

This looks like your tables are not normalised:-

[EXT NOTIFY DATE 1] &','& [EXT NOTIFY DATE 2] &','& [EXT NOTIFY DATE 3] &','& [EXT NOTIFY DATE 4]

Can you upload a copy of your database with some random unclassified data?

[Welshgasman]

Isn't anyone kind or understanding anymore? I thought this was a forum where you could come and get help, or am I wrong? I DID Google, and none of the sites, including the one you provided, helped me with my issue. I'll try to find another site where perhaps people are more willing to help than put others down or make them feel dumb for asking for help. Have a great day!

Don't throw your toys out of the pram?
A simple google would give you the syntax for a dlookup, then you just need to get the criteria correct.
I tend to put the criteria into a string variable, so I can debug.print it until I get it correct. Then I can use that string variable in the domain function?
You appear to want to retrieve 4 fields, which are numerically suffixed, which gives the impression your structure is incorrect?
TBH I cannot see how you would get null, when you concatenate , between the field values?
You also appear to be using a very advanced method of dlookup, that is rarely seen?
Perhaps explain in plain words how all these dates work together, plus how the queries for each date come into it?