Results 1 to 13 of 13
  1. #1
    Join Date
    May 2013
    Posts
    14

    Unanswered: Access Queries and variables

    I am a relatively new user of MS Access and find myself in constant need of "enlightenment".

    I have a query that uses ODBC connection to several tables in a proprietary database.

    I have two 'contrived' fields:

    FieldName1: ""


    FieldName2: ""

    I would like to be able to autopopulate each record in the resulting query with a constant value for each of the datafields:

    FieldName1 would autopopulate with the value "MyCompany"

    FieldName2 would autopopulate with the value "MyLoanData"

    each time the Select statement is run.


    Any ideas on how I can accomplish this?


    Any help will be greatly appreciated...

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    You could use an Update query to just populate those two fields.

    Code:
    UPDATE tbl SET tbl.FieldName1 = 'MyCompany',  tbl.FieldName2 = 'MyLoanData';

  3. #3
    Join Date
    May 2013
    Posts
    14
    Thank you for your quick response....


    If I understand your answer.....this "UPDATE" is intended to update the tables in the database??

    If this is the case, I won't be able to use this because the database in Read Only and I don't have the option to write anything to it, including the creation of views...

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    Is your intention just to display your two fields at the time of creating a report or query? If so, in your Select query you can assign these values. Where do you get the "MyCompany" info from? Is this another table or within the same table your Select query is based upon?

  5. #5
    Join Date
    May 2013
    Posts
    14
    "MyCompany" is just an example of the data value I would like to see populated in the FieldName1 column for all records returned in the select query

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    I wanted to give you an example of syntax to refer to where you are getting your MyCompany from. If it is an existing piece of information contained in the table(s) used in your Select query that would be one syntax. If it is contained on a table not used, that would be different syntax.

  7. #7
    Join Date
    May 2013
    Posts
    14
    The data value that I wish to populate in the FieldName1 column is simply an arbitrary data value that I need to populate in every record of the select query result set in that particular field. It is not stored in any table or database.

    When the select query is run, I would like every record to contain the data value "MyCompany" in the FieldName1 column.

  8. #8
    Join Date
    Jan 2005
    Posts
    146
    Easy, in that column put the info you want to show:

    FieldName1:"MyCompany"

    and it will show for each record.

  9. #9
    Join Date
    May 2013
    Posts
    14
    That worked beautifully.....thanks a ton....

    Now on to my second problem......I have another 'contrived' column 'Data_Period' for which I want to populate the date of the last day of the prior month. i.e. for a report running in May, 2013, I would need the date that shows up in the "Data_Period" column to be 2013-04.....

  10. #10
    Join Date
    Jan 2005
    Posts
    146
    try:

    LastDayMonth = DateSerial(Year(date()), Month(date()) , 0)

    If you want to tell it the date, replace date() with the field name of your date.

  11. #11
    Join Date
    May 2013
    Posts
    14
    I want to make sure I'm clear....

    I currently have a select statement

    Select "" AS Data_Period
    FROM...


    I want to use:

    Select "DateSerial(Year(date()), Month(date()) , 0)" as Data_Period
    From...



    Or have I totally confused the issue...

  12. #12
    Join Date
    May 2013
    Posts
    14
    OK.....tried that and it works but returns the date as "4/30/2013"....any way to force that to be "2013-04"

  13. #13
    Join Date
    May 2013
    Posts
    14
    I think I came up with the answer using what you provided along with the Format function.....


    Thanks a million for your assistance....

Posting Permissions

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