asked on

DFIRST error

I am getting an error on the following line of code:
z = DFirst("[DealingDate]", "Dealing_Dates ", "[DealingDate] >= " & strDate & " AND CompanyID = " & lngID & "")
It worked up until I changed the from Numeric to Text.

I get a Runtime error 2471. The expression you entered as a query parameter produced this error: 'C000001'
'C000001' is the data in the variable lngID.

The Table "Dealing_Dates has the field CompanyID.

Any ideas?

Sub DIR_TransactionDateCalc()

Dim x, y, strDate, lngID
Dim w, z As Date

[Forms]![Dealing Input]![TransactionDate].Visible = True

If [Forms]![Dealing Input]!TransactionType = "Issue" Then

x = DLookup("[SubscriptionCutDay]", "CTP ", "[CompanyID] = [CID]")
y = DLookup("[SubscriptionCutTime]", "CTP ", "[CompanyID] = [CID]")

If [Forms]![Dealing Input]![Received Time] > y Then
x = x + 1
End If

w = [Forms]![Dealing Input]![Received Date] + x

strDate = Format(w, "\#yyyy\/mm\/dd\#")
lngID = [Forms]![Dealing Input]![CompanyID]
'On Error GoTo Form_Load_Err
z = DFirst("[DealingDate]", "Dealing_Dates ", "[DealingDate] >= " & strDate & " AND CompanyID = " & lngID & "")

[Forms]![Dealing Input]![TransactionDate] = z
[Forms]![Dealing Input]!OfferPrice.Visible = True
[Forms]![Dealing Input]!BidPrice.Visible = False
[Forms]![Dealing Input]!RedemptionCharge.Visible = False
[Forms]![Dealing Input]!OfferPrice.SetFocus
End If

If [Forms]![Dealing Input]!TransactionType = "Redemption" Then
x = DLookup("[RedemptionCutDay]", "CTP ", "[CompanyID] = [CID]")
y = DLookup("[RedemptionCutTime]", "CTP ", "[CompanyID] = [CID]")

If [Forms]![Dealing Input]![Received Time] > y Then
x = x + 1
End If

w = [Forms]![Dealing Input]![Received Date] + x
'w = CVDate(DateAdd("d", x, [Forms]![Dealing Input]![Received Date]))

strDate = Format(w, "\#yyyy\/mm\/dd\#")
lngID = [Forms]![Dealing Input]![CompanyID]
'On Error GoTo Form_Load_Err
z = DFirst("[DealingDate]", "Dealing_Dates ", "[DealingDate] >= " & strDate & " AND [CompanyID] = " & lngID & "")

[Forms]![Dealing Input]![TransactionDate] = z
[Forms]![Dealing Input]!OfferPrice.Visible = False
[Forms]![Dealing Input]!BidPrice.Visible = True
[Forms]![Dealing Input]!RedemptionCharge.Visible = True
[Forms]![Dealing Input]!BidPrice.SetFocus
End If


Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox ("There is no available Dealing Date. Please update the Dealing Dates before inputting this deal!")
    Resume Form_Load_Exit

End Sub

Open in new window

Jim Dettman (EE MVE)

This:
z = DFirst("[DealingDate]", "Dealing_Dates ", "[DealingDate] >= " & strDate & " AND CompanyID = " & lngID & "")
Needs to be:
z = DFirst("[DealingDate]", "Dealing_Dates ", "[DealingDate] >= " & strDate & " AND CompanyID = " & lngID)
and this:
strDate = Format(w, "\#yyyy\/mm\/dd\#")

needs to be:
strDate = Format(w, "\#mm\/dd\/yyyy#")
JimD.

Jim Dettman (EE MVE)

And BTW, DFirst() is the wrong choice as it will give you the first record in the table. You probably want DMIN().
JimD.

Doi7722

ASKER

JimD
I have inserted your code and it still produces the same error. Do you have any further ideas. As I said before this only stopped wotking when the Data became text from a Number before.
I have changes to DMIN. the date format needs to be like that otherwise I get the incorrect date when using the UK Date format.

Jim Dettman (EE MVE)

<<It worked up until I changed the from Numeric to Text.>>
Changed the Dealing_date or companyID?
JimD.

Doi7722

ASKER

The CompanyID was nmeric and I changed it to text.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

gustav has posted the corrected code. You might also want to take a read through this:
http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Dlookup-and-the-Domain-Functions.html
Which I wrote and covers passing strings as arguments within a string.
JimD.

Doi7722

ASKER

JimD
Thanks for your help and will read you article.
Thanks