Please help - formula query

[TonyW1234]

Hi all,

I currently have to display a large amount of data on a separate report. I have a master sheet which has columns and cells linked to another sheet, then a further sheet which has cells linked to the master sheet. The problem I am getting is when I link some of the date columns using a formula in the 3rd sheet, as this is looking at a cell that has another formula in on the other sheet it is displaying a date of 00/01/1900 if the value is zero, is there any easier way of getting the data to just display a blank as this is messing up the database it is being exported to.

Any help would be much appreciated.

Many thanks

Tony

 

[Fazza]

This gives a flavour of what I did. It was just for a learning exercise. Being able to create a .mdb file and table had eluded me but I found code online to do that as referenced in the comments at the top of the relevant code.

Option Explicit

Dim mobjConn As Object

Sub SeeIfItWorks()

  Dim str_DB_Table_Name As String
  Dim str_DB_File_Name As String
  Dim str_DB_File_Path As String
  Dim str_XL_Data_Range As String

  str_DB_Table_Name = "tblStaffingPlan"
  str_DB_File_Name = Format$(Now, "hh mm ssss") & ".mdb"
  str_DB_File_Path = "M:\DataFiles\"
  str_XL_Data_Range = "tblData"

  Set mobjConn = CreateObject("ADODB.Connection")
  Call CreateDbTable(str_DB_Table_Name, str_DB_File_Name, str_DB_File_Path)
  Call UpdateDbTable(str_DB_Table_Name, str_XL_Data_Range)
  mobjConn.Close
  Set mobjConn = Nothing

End Sub

Private Sub CreateDbTable(str_Table As String, str_File As String, str_Path As String)
  'Refer to www.excelguru.ca/node/60
  Dim obj_ADOX_Catalog As Object
  Dim strConn As String
  Dim strSQL As String

  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & str_Path & str_File

  strSQL = Join$(Array( _
      "CREATE TABLE", str_Table, _
      "([Source] Text(127) WITH Compression,", _
      "[Function] Text(127) WITH Compression,", _
      "[SubFunction] Text(127) WITH Compression,", _
      "[Category] Text(15) WITH Compression,", _
      "[Grade] Text(15) WITH Compression,", _
      "[Company] Text(127) WITH Compression,", _
      "[Company_Category] Text(15) WITH Compression,", _
      "[Location] Text(127) WITH Compression,", _
      "[Family] Text(15) WITH Compression,", _
      "[Assignment] Text(15) WITH Compression,", _
      "[Phase] Text(127) WITH Compression,", _
      "[Month] Date,", _
      "[Cost_Group] Text(15) WITH Compression,", _
      "[Cost_SubGroup] Text(127) WITH Compression,", _
      "[Hours] Decimal(6),", _
      "[Cost] Decimal(6))"))

  Set obj_ADOX_Catalog = CreateObject("ADOX.Catalog")
  obj_ADOX_Catalog.CREATE strConn

  With mobjConn
    .Open strConn
    .Execute strSQL
  End With

  Set obj_ADOX_Catalog = Nothing
End Sub

Sub UpdateDbTable(str_Table As String, str_XL_Data_Named_Range As String)
  
  Dim strFields As String
  Dim strSQL As String
  Dim strXL_File_Name As String

  strXL_File_Name = "M:\DataFiles\Book1.xls"

  strFields = "Source, Function, SubFunction, Category, Grade, Company, Company_Category, Location, Family, Assignment, Phase, Month, Cost_Group, Cost_SubGroup, Hours, Cost"

  strSQL = Join$(Array( _
      "INSERT INTO [" & str_Table & "]", _
      "SELECT " & strFields, _
      "FROM", _
      "(SELECT " & strFields, _
      "FROM [" & str_XL_Data_Named_Range & "] IN '" & "M:\DataFiles\Book1.xls" & "' 'Excel 8.0;'", _
      "UNION ALL", _
      "SELECT " & strFields, _
      "FROM [" & str_XL_Data_Named_Range & "] IN '" & "M:\DataFiles\Book2.xls" & "' 'Excel 8.0;')", _
      "WHERE Month >= " & CLng(DateSerial(2007, 7, 1))), vbCr)

  mobjConn.Execute strSQL
End Sub

 

[Yogi Anand]

Only difference Excel-Excel v Access-Excel is the connection string. So, that will be trivial for you.

With a little ADOX you can create an .mdb file on the fly. I did a neat little test recently and with a relatively few lines of code created an .mdb file with new table (ADOX) and then (ADO) pulled data from mulitple Excel files into the new file. As I late bound, this could be run from 'anywhere'. So, even without Excel or Access even being loaded on the machine, the code ran fine from VBA in MS Outlook or MS Word. Another bit of magic - no Excel, no Access and a new mdb file is created from multiple Excel files!

Hi Fazza:

Sounds very interesting ... I would love to see one of these projects you have worked on. Perhaps we can pick it up offline and exchange notes.

 

[Fazza]

Sounds very interesting ... I would love to see one of these projects you have worked on. Perhaps we can pick it up offline and exchange notes.

Sure, Yogi.

I posted as you were writing your message. The sample code is above, just before your post. Regards, Fazza

 

[Fazza]

Alexander,

A minor overnight thought about using ADO from Excel sources - related only to details of the SQL, not the ADO. I think it is a little different from the SQL used in MS Access. When setting/changing a field name in the SQL, safest to enclose the table name in brackets. (Not that you need to, it just seems best; it is not necessary if there are no spaces in the field name, but I find I use the brackets always.) So,

SELECT field_1 AS [FieldName], sum(field_2) AS [Total]

Another thought, it seems functions usable in VBA can be used in the SQL. So a bit of work can be done in the SQL that way. And you can use

SELECT Iif(test of field,whatever,alternative)

I hardly ever use MS Access so maybe this is routine & familiar?

HTH, Fazza

 

[xenou]

Thanks - I appreciate the tips. I will watch these brackets - I've noticed that the author of Programming Access with VBA (for Dummies) uses them frequently as well.

The IIf Function in Access is indeed a common and useful function when writing queries in Access Query Design View -- I found out this out in reverse scratching my head how to do this with T-SQL (eventually found my way to Case structure in that environment).

Regards.

(Greetings to Yogi Anand as well)

 

[Fazza]

OK, the IIF is 'normal'. For sure others are, things like text functions (left, mid, etc), mathematical (INT, %, MOD, etc), there really are lots.

I have tried but had no success using a CASE statement. Have you used them in SQL within Excel? I'd love to have an example to copy!

 

[Fazza]

PS. Please, what is T-SQL?

 

[xenou]

no success using a CASE statement. Have you used them in SQL within Excel?

No, not in Excel - I've used it at work connected to an MS SQL Server database. T-SQL meaning Transact-SQL, Microsoft's procedural extension of SQL standard. I guess I use it as shorthand to mean working natively in a SQL Server Database. They let me have some SQL tools here after I begged for a year and a half (I am an accountant, not an IT professional) -- so I run ad hoc queries to get business data.

This is the code structure I came up with for SQL Server, to get to the same logic as I use with IIF in Access:

SELECT Field1, 
	CASE
		WHEN SUBSTRING(Field1,1,4) like 'TEST' 
		THEN 1
		ELSE 2
	END
	AS NewField
FROM Table1

 

[Fazza]

Thanks for the explanations. I have seen examples with CASE in SQL, though none that work purely within Excel.

 

[Fazza]

Another late thought. Earlier I commented about it had been difficult for me to create an .mdb file & table (from Excel VBA). In fact I had found many examples of this; using DAO and early bound.

However I wanted to distribute the files and be sure that they would still work when other users didn't have the DAO references set. So, I was really after a late bound solution. And I hadn't worked that out with DAO. The ADOX I could easily late bind, as in the sample I posted.

It is a significant point and the reason for me re-posting: the example above is late bound and works without references to ADO & ADOX.