Posts by james_skeggs

Re: Null values for integer from SQL query shown as 1 when using vbnull

In the recordset that I populated above, it is holding the null values as VbNull, which if just put into a spreadsheet gives me a "1".

I solved this issue by adding an additional IIF() statement when I iterate through the recordset to populate the spreadsheet:

ThisWorkbook.Worksheets(myTab).Range("O" & iRow + i) = IIf(dom(i) = vbNull, "", dom(i))

Rather then my old code which was:

ThisWorkbook.Worksheets(myTab).Range("O" & iRow + i) = dom(i)

Hello All,

I have an SQL query that is returning null values correctly for some items. These items may be integers or string values.

When I am populating a recordset with the values from this SQL query, I am hitting an issue with variables that are integers.

I am currently using the following code:

lmr(i) = IIf(IsNull(.Fields("bob")), vbNull, .Fields("bob")

The issue with this is that it gives me a value of "1" when I subsequently populate a spreadsheet with that item from the recordset rather than null or empty.

I tried using vbNullString instead of vbNull, but I get a type mismatch.

Hi,

I have an access 2000 database that is accessed by ~6 users. I have recently started getting a problem where a user tries to open the database, and they get a message saying that the database is locked. Open as read only?

This happens even though there is no one in the database.

Has anyone encountered this type of problem before, and is there any way round it?

Yours,
James

Re: MATCH function with a two or more separate ranges

Hi

I believe it is because as with VLOOKUP, you can only do the find in one column... i.e. A1:A5 rather than B79:IV79 or B161:C161

Re: Summarize Data in Another Layout

Thanks Jim.

That is perfect!

Re: Summarize Data in Another Layout

Any thoughts anyone please?

Re: Summarize Data in Another Layout

Dave,

I should also point out that there may be days where the number is 0.

Please see an updated attachment as an example.

James

Re: Summarize Data in Another Layout

Sorry Dave,

I thought I had simplified my first attachment enough.

Clearly not!

Re: Summarize Data in Another Layout

The data feed says that:

From the 1st of July to the 15th of July, Company A had 2300.
From the 16th of July to the 21st July, Company A had 700

Thus for Company A, the column in the required format should be:
Number on 1st: 2300
Number on 2nd: 2300
Number on 3rd: 2300
etc etc
Number on 15th: 2300
Number on 16th: 700
Number on 17th: 700

Re: Summarize Data in Another Layout

This is the way I can get the pivot table to display the data

Re: Summarize Data in Another Layout

Thanks,

But i can't get a pivot table to give me the days inbetween as shown in my example sheet.

Is there a certain option setting in pivot tables that allows you to do this?

Hi,

I have a data feed that gives me a summary of a set of data. I want to be able to work back to what would be the original data (this unfortunately isn't available). The attached excel file as an example of what I am trying to do.

I am not too sure about the working with the dates etc.

I would be much appreciated if someone could help.

Yours,
James

Re: If formula that works only if number is direct multiple of 3

Perfecto!

Hi,

I would like to have the following in a spreadsheet (not in VBA)

In column A, have a list of daily dates.

01/10/2005
02/10/2005
03/10/2005
04/10/2005

etc.

Then in column B (in B1) a formula that does this:

If Month(A1)<>Month(A2) AND

Month(A1) is a direct divisor of 3 (e.g. 3, 6, 9, 12)

Then give me "XXX" otherwise give me ""

Many thanks,
James

Hi,

Is there any way to determine a computers default e-mail client?

I am looking to send a spreadsheet to a number of people, and they can have either OUtlook or Lotus Notes.

I want a way for them to send an e-mail back automatically, through a user form on the spreadsheet without them having to save it and then attach it and e-mail back.

Kind regards,
James

Hi,

I have a report in Access based on a query, one of which fields is a Yes/No (boolean) field.

I wanted to know if it is possible to format the report so that I can have rows that have the Yes/No field = true to be in Bold, but the others (i.e. Yes/No Field = False) are just in Normal text.

Can anyone help me?

Re: complicated formula

Possibly something that you could do using pivot tables... Have you tried that route?

Re: View spreadsheet in VBA user form

Sure will post when finally finished, and looking nice

Re: Delete Specific Rows

I think this does what you want:

Sub Hide_Rows()
    Dim end_row As Integer
    end_row = Cells(65536, 1).End(xlUp).Row
     
    For i = 2 To end_row
        If Cells(i, 1).Value = "0" Then
            Rows(i).EntireRow.Delete
            i = i - 1
        End If
    Next
End Sub

Re: macros within macros

try

Call xxx

where xxx is the name of your macro