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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

DateAdd Function error

In the below Code The DateAdd function is throwing the following error, Compile error: argument not optional, Valid is a number, and BidDate is a date, but it appears to be looking for a string where I put day. on another note I inherited this program from the person who worked here before me and I think you have to go back five or so people before you reach the person who actually wrote the code, my point is that this function was used else where in the code and it works fine so what am I doing wrong.
Please and Thank you
Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, BidHist.Cost, BidHist.EstWt, BidHist.MOQ, BidHist.Lead, BidHist.FOB, BidHist.BidDate, BidHist.Valid, BidHist.Notes From BidHist Where CatNum = " & fa.TextMatrix(rowctr, 5) And DateAdd(day, Valid, BidDate) >= "Date")

Open in new window

0
samme
Asked:
samme
3 Solutions
 
HainKurtSr. System AnalystCommented:
what is "Date" at the end of the query?

are you trying to run something like this?

Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, BidHist.Cost, BidHist.EstWt, BidHist.MOQ, BidHist.Lead, BidHist.FOB, BidHist.BidDate, BidHist.Valid, BidHist.Notes From BidHist Where CatNum = '" & fa.TextMatrix(rowctr, 5) & "' And DateAdd(day, Valid, BidDate) >= GetDate()"
0
 
HainKurtSr. System AnalystCommented:
and what is target database? access, sql, oracle,...?
0
 
GrahamSkanRetiredCommented:
The first argument for the DateAdd function must be string, and you must compare the result to a date, not a string.

e.g.

DateAdd("d", 7, Now()) >= CDate("Jan 6 2010")

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sammeAuthor Commented:
First sorry it took so long to get back to you,
HainKurt
Date produces today's date, That's working other places in the code, and it is and it is an sql database

Graham Skan
I will try that see what happens
0
 
sammeAuthor Commented:
OK lets try this a different way, Valid represents a number of days, I need the date, thats that many days past biddate, and then I need to know if that date is greater than are equal to today, like I said I inherited this code and I was trying to use the same functions as the guy who wrote it, I do appretiate any help I get
0
 
HainKurtSr. System AnalystCommented:
Date does not produce anything in sql, are you sure it is used somewhere else and working?
in sql we have GetDate() to get the current datetime, and

DateAdd(day, Valid, BidDate) >= GetDate()

should be ok if Valid is integer

Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, BidHist.Cost, BidHist.EstWt, BidHist.MOQ, BidHist.Lead, BidHist.FOB, BidHist.BidDate, BidHist.Valid, BidHist.Notes From BidHist Where CatNum = '" & fa.TextMatrix(rowctr, 5) & "' And DateAdd(day, Valid, BidDate) >= GetDate()"
0
 
sammeAuthor Commented:
Valid is an Integer and in the BidHist Table(same as CatNum) I tried pulling it as simply Valid and BidHist.Valid and still get the Variable not defined error, will I have to declare this before hand and set it to the proper values?
0
 
HainKurtSr. System AnalystCommented:
try [Valid] or 'Valid'
I guess it is a reserved word (bad habit to use reserved words as column names ;)
0
 
HainKurtSr. System AnalystCommented:
no comment about 26149320???
0
 
HainKurtSr. System AnalystCommented:
maybe you should use "Date()" instead instead of just "Date"

0
 
aikimarkCommented:

Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, BidHist.Cost, BidHist.EstWt, BidHist.MOQ, BidHist.Lead, BidHist.FOB, BidHist.BidDate, BidHist.Valid, BidHist.Notes From BidHist Where CatNum = " & fa.TextMatrix(rowctr, 5) & " And DateAdd(day, Valid, BidDate) >= Date()")

Open in new window

0
 
sammeAuthor Commented:
on 26149320 it is an SQL Database
0
 
sammeAuthor Commented:
The below line of code runs without a problem on a different Module in the same Project, The DateAddfunction runs fine here and of day, Valid, and BidDate None of these are declared any where before this line, I searched and compaared his code to mine and found no reason this shouldn't work, I hope this helps.
Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, VendHdr.Terms, BidHist.BidDate, BidHist.Valid, BidHist.Lead, BidHist.MOQ, BidHist.ExchRate, BidHist.Currency, OurCost = (BidHist.Cost * BidHist.ExchRate) From BidHist, VendHdr Where CatNum = " & fa.TextMatrix(rowctr, 2) and DateAdd(day, Valid, BidDate) >= '" & Date & "' and Cost > 0 and NoBid = 0 And BidHist.VendNum = VendHdr.VendNum Order By OurCost", dbOpenSnapshot)

Open in new window

0
 
sammeAuthor Commented:
I was able to figure it out thank you for the help, I was able to get it running, sorry it took so long for me to get back with you guys, I will distribute the points among everyone who helped.
Thanks again
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now