Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DFIRST error

Posted on 2009-12-30
8
Medium Priority
?
323 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:Doi7722
  • 4
  • 3
8 Comments
 
LVL 58
ID: 26144911
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.
 
0
 
LVL 58
ID: 26144923
And BTW, DFirst() is the wrong choice as it will give you the first record in the table.  You probably want DMIN().
JimD.
0
 

Author Comment

by:Doi7722
ID: 26144988
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 26145348
<<It worked up until I changed the from Numeric to Text.>>
Changed the Dealing_date or companyID?
JimD.
0
 

Author Comment

by:Doi7722
ID: 26151959
The CompanyID was nmeric and I changed it to text.
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 26152659
Modify and review as shown.

/gustav
Sub DIR_TransactionDateCalc() 
 
Dim strDate As String
Dim strID As String
Dim w As Date
Dim x As Date
Dim y As Date
Dim 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 
  ' Reconsider this. It makes no sense to add dates.
  ' A date and a time, however, can be added.
  w = [Forms]![Dealing Input]![Received Date] + x 
 
  strDate = Format(w, "\#yyyy\/mm\/dd\#") 
  strID = [Forms]![Dealing Input]![CompanyID] 
  ' On Error GoTo Form_Load_Err 
  ' Note: Space removed.
  z = DFirst("[DealingDate]", "Dealing_Dates", "[DealingDate] >= " & strDate & " AND CompanyID = '" & strID & "'") 

End If 

Open in new window

0
 
LVL 58
ID: 26153087
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.
0
 

Author Comment

by:Doi7722
ID: 26153419
JimD
Thanks for your help and will read you article.
Thanks
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question