Results 1 to 9 of 9
  1. #1
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34

    Casting Long date AND Just a Month Number

    I'm creating a query in Access 2010. My query is calling for a long date (Full date and time). I also want a second column to show me just the month number. I have two columns calling to 'Ord_Bookdate' from table 'DBO_Orderheader' to get my date. I looked on the inter-web and found that naming the second column's field as " Ord_BookdateMonth([Ord_Bookdate])) " DOES cast my date from the long date to just the month number as long as I do not add the table name of where that field is located. When I add the table name, I get the popup message " Extra ) in query expression 'dbo_orderheader.[Month([order_bookdate])) ". Now, I would just leave it be and move on; HOWEVER, I need dates for other things as well that are on different tables and, because some of the field names are the same on different tables, I need to define which table I want the field from; otherwise, I would get the error message " The specified field '[stp_arrivaldate]' could refer to more than one table listed in the FROM clause of your SQL statement. "



    Soooo, I need to know how I can have the long date and just the month number on the same query that is pulling from the same field and the same table...

    I've attached three pics that show the popup messages and the way my query is set up. PLEASE help me figure this out.

    Thanks in advance.Click image for larger version. 

Name:	Refer to More Tables.jpg 
Views:	13 
Size:	20.1 KB 
ID:	30615Click image for larger version. 

Name:	Extra in Expression.jpg 
Views:	13 
Size:	17.2 KB 
ID:	30616Click image for larger version. 

Name:	Query Setup.jpg 
Views:	13 
Size:	43.6 KB 
ID:	30617

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you should not be using brackets in your field names - it is bound to cause confusion. As is having spaces and using reserved words such as Order, Date. If you want spaces, use the caption property.

    you also cannot have two columns named the same or using the same name as one of the fields in the table - which this implies Ord_BookdateMonth([Ord_Bookdate])). And would appear to have an additional ) at the end

  3. #3
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    Thank you for the reply. The brackets automatically get put on there. I did remove them and when I press the run button, the brackets auto populate in the field name again. Still stuck...

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    square brackets will automatically be added, round ones won't - so your Order Book Month column should be Month([Ord_BookDate]), not (Month([Ord_BookDate])) and if you add the table name it should be Month([dbo_orderheader].[Ord_BookDate])

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, using reserved words in aliases (captions) shouldn't cause a problem. Nor should spaces or special characters. Am I wrong about that? I just tested Month: as a query field alias and it ran with no problems.
    Not saying this would be true outside of Access (such as sql databases)...
    Would appreciate a link that says otherwise if you know of any.
    Thanks.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't know about aliases (which in query builder would be myAlias: field1), but it should not affect captions which is a property which is why I suggested using a caption - in the query, right click on the column and select properties. Note this is a feature of Access not available (at least not without work) in other DBRMS.

    Month is not a reserved word per se - but it is the name of a function which is how the OP is trying to use it. Benefit over using the format function is it returns a numeric value rather than a text value.

    This is a link to Access reserved words which has the appropriate warnings https://support.office.com/en-us/art...7-DA237C63EABE

    With regards spaces, non alphanumeric characters and reserved words in names and using square brackets to compensate, yes the square brackets solve the problem perhaps 9999 time out of 10000 (just a guess). But over the many years I have been using Access I have come across the very odd occasion where it doesn't and a very weird, non definitive error is generated. Removing spaces and nonalphanumeric solves the problem. Note that the problem may not materialise in SQL but in VBA code or even form or report design. Anecdotally, you will find perhaps 3 or 4 threads on this and other forums over the last few years where this has been the case - sorry, I don't have time to go find them.

  7. #7
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    THIS WORKED! THANK YOU!

    should be " Order Book Month: Month([dbo_orderheader].[Ord_BookDate]) " Pic of the way it is set up is attached. Thanks again!
    Attached Thumbnails Attached Thumbnails Month Number Fixed.jpg  

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    glad to help - for the future, recommend you provide SQL code rather than screenshots of the query window - much easier to read and for people to help. (Click on the SQL button top left of the ribbon or bottom right of the access window, then copy and paste)

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was confused by your statement not to use Month since it's a reserved word (which I'd agree with for field, object, etc. names) but I could only see where it was being used as a field alias. That's the reason for asking.

    As for field/alias, in table design an alternate field name is referred to as a caption. A query in design view using that field shows the actual field name in that table, not the caption, but the output uses the caption. In a query field with My Field:Field1 or some similar thing, "My Field" is an alias since when you read the sql, it will say Field1 AS My Field. The AS makes it an alias in my book, same as if I had aliased a table. If you add a caption as you suggest, it will use it in the datasheet view - in fact, it will override the table caption, but it won't change the sql to use AS.
    SO in a nutshell, when you wrote
    As is having spaces and using reserved words such as Order, Date.
    I figured you were saying don't use reserved words when aliasing a query field. I couldn't find any info on that.
    Thanks.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-29-2017, 12:11 AM
  2. enter number for month
    By slimjen in forum Queries
    Replies: 2
    Last Post: 01-07-2016, 11:25 AM
  3. Formatting Long Date
    By kdbailey in forum Access
    Replies: 5
    Last Post: 01-22-2015, 11:01 AM
  4. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  5. Replies: 4
    Last Post: 05-26-2013, 03:28 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