Joining tables with a Calc field

[Gordy]

Do you see any problems with this calc field?____VAL(RVMANIFE.Domicile)+RVMANIFE.UDateTime+VAL(RVMANIFE.Trip_ID)+RVMANIFE.Stop_Number____Which I would like to use to join to another table using this calc field____VAL(RVBILLS.Domicile)+RVBILLS.uDateTime+VAL(RVBILLS.Trip_ID)+RVBILLS.Stop_Number____Domicile = text__UDateTime = Integer__Trip_ID = text__Stop_Number = Integer____I would like to join the "RVMANIFE" table and the "RVBILLS" table via these two fields.____Any idea why they are not available to join with? I am using your SQL version 8.1____Gordy

[kfleming]

For a calculation to be available as a SQL join field, it must be engine evaluatable so that it can be passed to the SQL engine as part of the SELECT.____If a calc is evaluatable, it will have a (x) listed on the status bar line at lower left.____What determines what can be translated is contained in the file RSWSQL.INI. It is in the RSW.EXE directory and is a self documenting text file with sections that influence SQL platform dependent behaviour.____Kathleen__R&R Support

[Gordy]

I am going against an MS Access 2000 MDB file. I also looked at that INI file and have no idea what to change in it to make those calc fields work for joining.____Can you step me through it and tell me what to edit in the INI file?____Gordy

[kfleming]

The Access section of the file does not have an entry for the VAL() function. So you want to edit it to add the last line shown below. Once you do this, a VAL() calculation will show up with the (x) in the Designer.________;;;;;;;;;;;;;;;;;;__;;; ;;;__;;; Access ;;;__;;; ;;;__;;;;;;;;;;;;;;;;;;__[MSAccess]__DataSourceDBMS = ACCESS__DataSourceDriver = ODBCJT32.DLL__JetDBFilter = 1__DataSourceDBTerm=DefaultDir__UseFileExt = 0__AddWhereFalse=0__DataSourceDriverID=FIL__DataSourceUsesDirPrefix=0__; operators__CAT = %1 & %2__TCAT = RTRIM(%1) & %2__; functions__LEFT = LEFT(%1, %2)__LOWER = LCASE(%1)__LTRIM = LTRIM(%1)__RIGHT = RIGHT(%1, %2)__RTRIM = RTRIM(%1)__SUBSTR = MID(%1, %2, %3)__TRIM = RTRIM(%1)__UPPER = UCASE(%1)__VAL = VAL (%1)__

[Gordy]

It worked like a charm..... Thanks Kathleen__

[FRED_EMMERICH_(Guest)]

Kathleen, is there a valid SQL for MS Access for the AT() function?____Thanks,

[kfleming]

I thought that the LOCATE function should work but I have been unsuccessful in getting it to work..____Kathleen