Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Primary Key

  1. #1
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Unanswered: Primary Key

    Some how my primary key was changed to random setting instead of increment. I can't change it back, so now if I want to print a report based on the date field when I sort the records they are sorted by:


    Date
    01-Aug-05
    01-Sep-05
    02-Aug-05
    02-Sep-05
    03-Aug-05
    03-Sep-05
    04-Aug-05
    04-Sep-05
    05-Aug-05
    05-Sep-05
    06-Aug-05
    06-Sep-05

    Is there anyway to get this to sort by actual date or is there a way to make a report or query sort by actual dates?

    Thanks
    Mike

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    If I understand you correctly, you used to sort on the primary key which conveniently meant that things appeared in date order (the order they were entered on the d/b). But your Primary key no longer follows that rule so you can do this anymore and you want to use the date field. But your date field is sorting as if it were text. Have I understood you correctly ?

    I suspect you are storing the date as text i.e. your table is set up with the date field as "text" rather than as "date/time". You should be able to just change it and the data will be converted (might be worth taking a copy of your table first though!!). Once converted, when you sort, it will sort in proper date order. You may need to format the output so you get the date displayed in your perferred format.

    hth
    Chris

  3. #3
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    primary key

    Chris that is sort of where I am, I still want to sort by the primary key but as long as it is random I can't. I resorted to sorting by the date field because of the random setting. What I am wondering is there a way to code the query or report to allow the dates to show in the right order? Or is there a way to code it so that I can change the random setting back to increment. I could always just remake the table and cut and paste all the info. That just seems like a lot of work if I don't really need to

    Thanks,
    Mike

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    So did you try my solution ? That's still the way to go I think.

    Yes it's possible to re-implement the increment.

    By increment I assume you mean autonumber. I'm not a fan of using autonumber to provide a chronological sequence. It's not secure and it's difficult to fix the sequence if it goes wrong. Stick with a dedicated date field.

    Yes it's possible to write code to sort your dates. But why ? Make you date field a proper date type and it will behave properly.

    Chris

  5. #5
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Primary Key

    Chris,
    I will try what you suggest,my only concern with going that way is that I have a calendar control tied into the date field as well. I will have to becareful to ensure that I don't mess it up. Anyway I am outta here for a few hours then I will try what you suggested and then let you know. By the way when I first put this db together I had it set to autonumber and increment, then I replicated the db, that is when I think the change was made to the primary key. Is that a reasonable assumption?

    Thanks
    Mike

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by AME1USN
    I will try what you suggest,my only concern with going that way is that I have a calendar control tied into the date field as well. I will have to becareful to ensure that I don't mess it up.
    take a backup of the whole database if you're a little unsure.

    Quote Originally Posted by AME1USN
    I replicated the db, that is when I think the change was made to the primary key. Is that a reasonable assumption?
    I'm not sure about that. It's quite easy to re-instate auto-number. Just delete the current PK field (after removing the PK and and reletionships). Then put it back in and set autonumber. It will sequence in the order that the data was entered. But I still advise against using it for time based sorting.
    Chris

  7. #7
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Primary Key

    Chris,
    I made the changes that you recommended, the changes worked as you knew that they would, however the changes messed up my calendar controls. Now when I double click on a date with in the calendar I get an error:

    If DCount("[ID]", "tblcomments", "[Date] = '" & Me.Combo4 & "'") = 0 Then


    Can you recommend anything?
    Thanks
    Mike

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Mike

    It looks there is also a field called Date in a table called tblcomments. It's trying to match this with the date field you just changed which of course it can't now. You should change the Date field in tblcomments to a proper date field as you did before which should do the trick (take another backup first). My only worry would be how how extensive this date problem is. Is this a big database ? Can you post it to the forum ? You did take a backup didn't you

    btw, it's considered bad practice to use the word "Date" as the name of a field. It can get you into all sorts of trouble if you start writing code because Date is a keyword and VBA will treat it as such.

    Chris

  9. #9
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Primary Key

    Chris,
    Yes I did make a back up, and good thing some how I messed it up doing something unrelated. regardless though as long as synchronize the replica after I screw something up I will be ok.

    Right now the db is not to large, it has the potential to get that way though. I had posted this db in another thread but that thread is gone now. The thread was titled "Calendar Control", a search does not bring it up anymore. That thread was about 1 month ago. I do have a copy of it at home though. I will post it after work tonight.

    Thanks
    Mike

  10. #10
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Primary Key

    Howey,
    Or anyone else who wants to take a stab at this, here is an example db. The db has a calendar control that works if the date in tblcomments is set to text. However I eed to be able to sort these entries and print reports. So at some point the Primary Key was set to random (I don't know how I managed that) and the date field was set to text.

    Anyway long story to short one... when I sort the dates the dates come out like they are in the first post of this thread. I could really use some help here. Thanks
    Mike
    Attached Files Attached Files

  11. #11
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Mike

    Dates have to be referenced with ## not ' '. Also, you no longer have a field called ID in you tblcomments so the count is revealing nothing. Did you remove it somewhere along the way ? There's one called s_GUID (?)

    So here's the fix...

    Go into the code for the form. (go into design mode for the form, then click View-Code or click the code icon)

    Change the line:
    If DCount("[ID]", "tblcomments", "[Date] = '" & Me.Combo4 & "'") = 0 Then
    to
    If DCount("[Date]", "tblcomments", "[Date] = #" & Me.Combo4 & "#") = 0 Then

    Also change
    Me.Filter = "[Date] = '" & Format(Me.Combo4, "dd-mmm-yy") & "'"
    to
    Me.Filter = "[Date] = #" & Me.Combo4 & "#"

    Should work then.

    A couple of points.

    You have a field called [Date]. This really scares me. It seems to be working ok but you may run into problems if you plan to develop further.

    Whoever wrote the code has had to do a lot of work to try and manage dates as strings. Managing dates as date types saves a lot of effort (formatting/matching/sorting etc).

    You form called frmaddcomment will not work at present I suspect for similar reasons. I haven't had chance to look at it yet as I have to go to work.

    Good luck.

    Chris

  12. #12
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65

    Primary Key

    Thanks Chris
    I am trying to learn access and VB so I write and deveope everything, with help of course fro generous people like you? I am going to put your fix to the real db tonight and run it. I think though that when I replicated this db that caused a lot of these problems.

    "s_GUID " was added by access when I replicated the db, I can't get rid of it.

    Anyway thanks I will let you know the results of the results on the real db.

    Mike

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I didn't read all of the responses, so I apologize if you already got this information. You can store the date as text, but then convert it to a date in a query to sort it. Use the CDate function in a query and it will convert (temporarily) the string to a date.

    In the query design put:

    ConvertedDate:CDate([StringDate])

  14. #14
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Mike

    DCKunkle's solution is by far the easiest way to go to get you up and running quickly in the short term. It's very easy to implement and requires no change to your original database (other than in a query). I feel foolish that I didn't think of it or suggest it myself Apologies for taking you down the hard route.

    The downside is you'll have to continue to work with a string date and hence use CDate whenever you want to perform date type functions. This may not be a problem if you don't plan to develop the d/b much further. If you are planning to develop your d/b then it's worth spending the effort to get this sorted asap imho.

    Chris

  15. #15
    Join Date
    Feb 2004
    Location
    Colorado state
    Posts
    65
    Chris or DCKunkle or anyone else,
    I have tried the: ConvertedDate:CDate([StringDate]) and I get errors. I am still a little green here. In the query does this expression go in the criteria box? I get errors if I put it in there. The error is probably because I missed something. Do I need to state a value or something else for the ([StringDate])? Such as:
    ([dd-mmm-yy])?

    Thanks
    Mike

Posting Permissions

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