Results 1 to 8 of 8
  1. #1
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10

    Question How to fill same data for a range of rows under a specific field

    Hi All

    I am new to Access. I have a table of 5 fields and more than 100,000 records(rows). I need to fill a certain range of rows(example 4,500 - 95061) under a specific field (column) with a fixed data say 12:05 AM. Could you help me how to do it? If I need any VB function please let me know the codes. Example:

    ID Fld1 Fld2 Fld3 Fld4 Fld5
    1 2 3 5 7 9


    ..
    4500 4 3 4 5 10
    ...
    95061 2 4 5 6 7

    Now I need to replace data's for ID 4500 - 95061 under Fld3 with "12:05 AM"


    Thanks

  2. #2
    PianoMan64 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    29
    Quote Originally Posted by nishezaman View Post
    Hi All

    I am new to Access. I have a table of 5 fields and more than 100,000 records(rows). I need to fill a certain range of rows(example 4,500 - 95061) under a specific field (column) with a fixed data say 12:05 AM. Could you help me how to do it? If I need any VB function please let me know the codes. Example:

    ID Fld1 Fld2 Fld3 Fld4 Fld5
    1 2 3 5 7 9
    ..
    4500 4 3 4 5 10
    ...
    95061 2 4 5 6 7

    Now I need to replace data's for ID 4500 - 95061 under Fld3 with "12:05 AM"


    Thanks

    You would write an update query as follows:

    Code:
    UPDATE NameOfTableToBeUpdated SET NameOfTableToBeUpdated.Field5 = #12:05 AM# 'Field5 MUST BE a Date Field
    WHERE (((NameOfTableToBeUpdated.FieldThatHasNumber) Between StartingValue And EndingValue))

  3. #3
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10
    So basically I have to press Alt+F11 to open VB editor and then type

    UPDATE 5Min_Bus SET 5Min_Bus.Time = #12:05 AM# 'Field5 MUST BE a Date Field
    WHERE (((5Min_Bus.Time) Between 4500 And 95061))

    And then run this macro.

    As my variables are
    NameOfTableToBeUpdated = 5Min_Bus
    Fileld5 = Time
    FieldThatHasNumber = Time
    StartingValue = 4500
    EndingValue = 95061

    Or please let me know how/where to write Update Query and run.

    Thanks for your help

  4. #4
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10
    Hi

    I have another questions on my original table. If I need to copy all the records(rows) and paste it consecutively say for 100 times from the new record row , how I need to do it? Where can I find all the source code/formatts/examples for Microsoft Access?

    I really appreciate your help.

    Thanks again

  5. #5
    PianoMan64 is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    29
    Hey nishezaman:

    To answer your first question, no you don't paste that the VB editor, it needs to be pasted in a new query.

    click on Query selection, then click create new query, then under View menu select SQL. Then paste the SQL Select statement in there then click back to Design mode. This will show you how it would be displayed in Design mode.

    You can then run it and see what you get.

    Thanks,

    Joe P.

  6. #6
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10
    Hi Joe

    I am gonna try your first suggestion.Please also help me to do my other question about copy and paste as I have to do all these on a regular basis.

    Thank you so much.

  7. #7
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10
    I just tried but it's giving me syntax error. When I opened SQL view the first 2 lines were there and then I pasted your code. It says missing operator in query expression
    '(((5Min_Bus.Time) Between 4500 And 95061))'
    In your original code isn't Field5 and FieldThatHasNumber are same? Why did you say ''Field5 MUST BE a Date Field? In my table "Time" is the name of my field and "12:05 AM" is a test/string. I also tried without the default two lines but it has the same error message. Please help.

    SELECT
    FROM 5Min_Bus;
    UPDATE 5Min_Bus SET 5Min_Bus.Time = #12:05 AM# 'Field5 MUST BE a Date Field
    WHERE (((5Min_Bus.Time) Between 4500 And 95061))

  8. #8
    nishezaman is offline Novice
    Windows Vista Access 2002
    Join Date
    Dec 2009
    Posts
    10
    I got the first problem working. Thanks for your help . Please show me some direction for my 2nd problem to select many rows and then copy and paste as new records many times to create a huge table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  2. Replies: 7
    Last Post: 11-29-2009, 01:44 PM
  3. Replies: 1
    Last Post: 11-10-2009, 03:20 PM
  4. find specific data
    By hoachen in forum Queries
    Replies: 3
    Last Post: 08-19-2009, 08:54 AM
  5. Automatic Data Entry on a Specific Date
    By alanbrai in forum Programming
    Replies: 2
    Last Post: 11-14-2007, 05:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums