Concatenating date field into text ands using in a formula

Concatenating date field into text ands using in a formula

Post by jason.preb » Fri, 12 Sep 2003 14:27:47


Hi all,

Hope you can help.

I have a date field (B1) and am trying to concatenate this into a
directory path as part of a formula to pull data from another
worksheet.
IE My worksheets are named open"date of report".xls
B1 contains "date of report"
I have tried concatenating the worksheet name in a field and then
using that field.
I have tried cocatenating the directory path in a field and using that
field.
My last attempt at a formula is

=SUM(IF('=concatenate("D:\CSA\Reporting\Open
Jobs\[open"&TEXT(B1,"ddmmyy")&".xls]Customer")'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Date is 110903, worksheet name is open110903.xls.

If I manually type it in as below the formaula works dream.

=SUM(IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$I$3:$I$96=$A$41,IF('D:\CSA\Reporting\Open
Jobs\[open110903.xls]Customer'!$C$3:$C$96<B48,1,0),0))

Any assistance or direction pointing would be greatly appreciated.
I hope I have provided enough info.

Cheers Jason
 
 
 

1. Concatenate Text Fields to Date Field

2. Concatenate Text field and date field

I currently have an Access 2003 database.

In a Student table, I have SSN field that stores the last four digits of
Social security number. I also have a DOB Date/Time field for Date of Birth.


I want to calculate my unique identifier in my form so that the "Identifier"
is SSN&DOB. (We originally had the full social security as the identifier,
but that is no longer allowed due to privacy restrictions).

My problem is that when I concatenate, I am getting, for example,
00041/30/1985 instead of 000431077. How do I show the date as a number
instead of a date format?

Is this possible?

3. Concatenate custom text fields into one Enterprise text field

4. can't find on date text in concatenated field

5. Between [high date] and [low date] formula using Data Type: Text

6. Concatenating rich text fields using lotus script

7. Excel:Get concatenated text to be recognised as formula not text?

8. concatenating 3 fields into a "date" field

9. How to Join/concatenate a date field with a time field in Excel?

10. structs: How to add a new DATE field as 3 concatenated fields

11. trouble concatenating text of 1 memo field to another memo field in same record via button

12. Concatenate text fields to make Field Name in table

13. Concatenate two fields (text & number) for key field

14. Can't Concatenate Text to Text Field

15. How can I concatenate a MEMO field and a text field?