# Anyone can help me perfect this function?

Derived from Here
Anyone can help me perfect the Y?

Dim Y As Integer
Dim M As Date
Y = 2018
M = #4/22/ &Y#

Me.txtbox1 = DLookup("[dNum]", "tblDate", "[NewDate] = #" & format(M,"YYYY-MM-DD") & "# and [Ename] = '" & [Ename] & "'")
Me.txtbox2 = Weekday(M)

txtbox1 should show the value of dNum .
txtbox2 should show 1 since 4/22/2018 is a sunday.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
``````M = DateSerial(Y, 4, 22)
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
It would seem to me that "Me.texbox1= " should should provide the value for (M)

Biggles1
Senior DeveloperCommented:
E.g.

``````' Your form method.
Private Sub Test

Dim Y As Integer
Dim M As Date

Y = 2018
M = DateSerial(Y, 4, 22)

Me.txtbox1 = DLookup("dNum", "tblDate", "NewDate = " & SqlDateJet(M) & " AND Ename = " & SqlQuote(Me![Ename]) )
Me.txtbox2 = Weekday(M)

End Sub

' Copy to a standard module.
Public Function SqlDateJet(ADate As Variant) As String

On Local Error GoTo LocalError

Exit Function

LocalError:
SqlDateJet = Format(Now, "\#m\/d\/yyyy#")

End Function

' Copy to a standard module.
Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String