Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Unanswered: Strange Access behavior

    Access is doing something I don't want it to do. (big surprise)
    I have some code running on the "On Load" event of a form. It prompts for a date, or to use the system date. Trouble is, it's not using the Date() from the system date. It thinks I want it to use a field called Date. I don't. It used to work, but since I actually put a date value in my field called Date, it's now looking at the code like it's a field. I only noticed because the date was wrong on my form and matched what I had entered into my table. So I changed the name of the field to something else, and now it just gives an error saying it can't find the field called Date. Here is the code.

    If MsgBox("Would you like to enter a date? If you answer no, I will go by your computers time.", vbYesNo) = vbYes Then
    strDate = InputBox("Enter date you would like to start with.", "Enter Date")
    Else
    strDate = Date
    End If

    Like I said, it worked perfect until I entered a value in the Date field. Is there a way to specify Date(), instead of a field named Date? I have no recordset or anything. The only thing before this code are Dim satements.
    I've never seen this before. I've tried changing to Now and it works fine. I just don't want the time. It's irrelavent to my application.

    It's like it's trying to "link" things for me, but I don't want it to. Any help is greatly appreciated.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Did you try recompiling the app? You can get the date using Date, Date(), or from an extraction from Now using the Format function (with or without using the CDate function) ...

    Did you have a variable or function named "Date"?

    Do you have Option Explicit declared?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Recompile - Yes
    When I specify Date() with parenthasis, VBA takes the parenthasis out when I move off the line.

    I don't have a variable, or function called Date.
    Yes, Option Explicit.

    I've never used the CDate function. I'll look into that.

    This is just strange.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You might in this instance need to Decompile the DB ... Then recompile it. How? Create a shortcut to your DB and add the \Decompile switch to the shortcut. Also, you will need to invoke Access explicitly to do this... Ex:

    c:\...\msaccess.exe MyDB.mdb \decompile
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Probably not the best idea to have a field called Date. Best option is to rename the field and Access won't get confused anymore.

    You can (and probably should) at least rename the text box control on the form linked to the Date field - call it txtDate or something.

    Quickest fix is probably to do as Owen suggests:
    Code:
    Format(Now(),"mm/dd/yyyy")
    were you want to insert the current system date.


    tc

  6. #6
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Quote Originally Posted by M Owen
    You might in this instance need to Decompile the DB ... Then recompile it. How? Create a shortcut to your DB and add the \Decompile switch to the shortcut. Also, you will need to invoke Access explicitly to do this... Ex:

    c:\...\msaccess.exe MyDB.mdb \decompile
    So I can't seem to figure this out. Does the database need to be in the Office11 folder where msaccess.exe is?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by padraig
    So I can't seem to figure this out. Does the database need to be in the Office11 folder where msaccess.exe is?
    No ... Drag to your desktop the acess db and create a shortcut to it. THEN modify that shortcut as I posted earlier ... Obviously, you do need to know where the Access executable resides on the hard drive ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Yes, I understand. I couldn't get it to work because I didn't know how to concatenate the location of msaccess.exe and the location to the .mdb. I figured it out. You used the wrong slash in your switch. It should be /decompile. Anyway, it didn't help. Thank you though. Using the format command did work in a way, but I need to modify the code after it so it handles this better. I have a work around, but it's just stupid what it's doing.

    Oh and Tcace, I never said the text box was named Date. It's not. It's called txtDate. The FIELD name in the table is called Date.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by padraig
    Yes, I understand. I couldn't get it to work because I didn't know how to concatenate the location of msaccess.exe and the location to the .mdb. I figured it out. You used the wrong slash in your switch. It should be /decompile. Anyway, it didn't help. Thank you though. Using the format command did work in a way, but I need to modify the code after it so it handles this better. I have a work around, but it's just stupid what it's doing.

    Oh and Tcace, I never said the text box was named Date. It's not. It's called txtDate. The FIELD name in the table is called Date.
    WELL ... That is where things went Kaboom on you. DATE is a reserved word. Do not use it as a column name ... The switch: it was one if them ...

    Back to the Date column: How are you referencing it? You SHOULD reference it (at all times!) like: [Date] You need those brackets ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Well that's just it. I'm NOT TRYING to reference the date field. I want to set strDate = Date()
    But when I move off the line, the brackets disapear and it automatically thinks I'm looking for a field calld Date. Now that I've changed the field name in the table, I get an error message saying it can't find the field called Date. Well no $hit!
    I think I'm going to give up on this. I have a work around, and I think I've learned a lesson, and that's that.

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by padraig
    Well that's just it. I'm NOT TRYING to reference the date field. I want to set strDate = Date()
    But when I move off the line, the brackets disapear and it automatically thinks I'm looking for a field calld Date. Now that I've changed the field name in the table, I get an error message saying it can't find the field called Date. Well no $hit!
    I think I'm going to give up on this. I have a work around, and I think I've learned a lesson, and that's that.
    Hmmm ... I think we (at least I got sidetracked ...) Did you try searching for an override ( function named the same) of the Date function in your entire project? Something has mangled your db file. Another thought: Try creating a new DB and import everything over. Try running and see if same behavior happens again.
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    That did it! I am only disapointed that I didn't do that earlier. I should know better. Thanks for all the help!

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by padraig
    That did it! I am only disapointed that I didn't do that earlier. I should know better. Thanks for all the help!
    You're welcome. What exactly did it? What fixed your problem?
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •