Problem in Acces with Cdate

0
August 17, 2011

Hello:

Here is the code which is a pop-up window to get the date, then it converts the date to a period:

1)

Public Function MaxMes(fecha) As String

'max day of month

Dim fech As Date

Dim mes As Long

    mes = Month(fecha) + 1

    If mes = 13 Then mes = 1

    fech = CDate("01/" & mes & "/" & Year(fecha))

    fech = fech - 1

    MaxMes = Format(fech, "dd/mm/yyyy")

End Function

2)

Public Function ConvierteAPeriodo(fecha) As Long

    If IsDate(fecha) Then

        ConvierteAPeriodo = Month(fecha) - 1 + ((Year(fecha) - 1900) * 13)

    Else

        ConvierteAPeriodo = -1

    End If

End Function





When I type in 12/06 or any december of any year it gives me the wrong date? How can I correct this error? it works fine with 1-11. Which seems strange to me.

Thanks for any insights.

Joseph

17

answers

Comments See all(0)

Add comment
0
August 17, 2011

> I need it to interpret the monthe and year and then transform it to a period,

That explains your trouble ...

Converting "month/year" to a date requires a little more validation.

Use a function like this:

Public Function DateFromMonthYear(ByVal strMonthYear As String) As Date

  Const cintDay           As Integer = 1

  Const cintMonthDecember As Integer = 12

  Const cintYearMinimum   As Integer = 1900

  Const cstrDateSeparator As String = "/"

  Const clngDimMonth      As Long = 1

  Const clngDimYear       As Long = 2

  Dim strDate   As String

  Dim datDate   As Date

  Dim intMonth  As Integer

  Dim intYear   As Integer

  If IsNumeric(strMonthYear) Then

    ' No date separator.

  Else

    ' Build full date string.

    strDate = CStr(cintDay) & cstrDateSeparator & strMonthYear

    If Not IsDate(strDate) Then

      ' Month or year cannot be resolved.

    Else

      ' Convert from local date separator.

      strDate = Replace(strDate, Format(Date, cstrDateSeparator), cstrDateSeparator)

      ' Retrieve month and year.

      intMonth = Split(strDate, cstrDateSeparator)(clngDim

Month)

      If intMonth > cintMonthDecember Then

        ' No valid month.

      Else

        intYear = Split(strDate, cstrDateSeparator)(clngDim

Year)

        ' Return the first of this month and year.

        datDate = DateSerial(intYear, intMonth, cintDay)

        If Year(datDate) < cintYearMinimum Then

          ' Prehistoric year entered.

          ' Return current date.

          datDate = Date

        End If

      End If

    End If

  End If

  If CDbl(datDate) = 0 Then

    ' No month/year found.

    ' Return current date.

    datDate = Date

  End If

  DateFromMonthYear = datDate

End Function

So:

  MaxMes = Format(DateFromMonthYear(f

ech, "dd/mm/yyyy"))

and:

  datDate = DateFromMonthYear(fecha)

  ConvierteAPeriodo = Month(datDate) - 1 + ((Year(datDate) - 1900) * 13)

But why 13? Couldn't you use DateDiff("m", #1/1/1900#, datDate)

/gustav

0
August 17, 2011

Hi Joseph,

To be safe, whenever I need a date conversion I always avoid ambiguous constructions.  So, use yyyy-mm-dd or

d-mmm-yyyy instead of dd/mm or mm/dd, and always use four digit years.

Regards,

Patrick

0
August 17, 2011

I would suggest:

 fech = format("01/" & mes & "/" & Year(fecha), "dd/mm/yyyy")

 fech = dateAdd("d", -1, fech)

0
August 17, 2011

input the "fecha" with #

 #12/06#  ' without the #, the code will interpret it as 12 divide by 06

0
August 17, 2011

try

fech = dateserial, year(fecha(,mes ,1)

0
August 17, 2011

fech = dateserial, year(fecha,mes ,1)

0
August 17, 2011

hello:

thanks...I've tried these suggestions, but I think the problem is with the converting to period part.

It is not accepting 12 for some reason....I think I am missing something.

cheers.

0
August 17, 2011

1)

Public Function MaxMes(fecha) As String

    'max day of month

    Dim fech As Date

    If IsDate(fecha) Then

      fech = DateAdd("m", 1, fecha)

      fech = DateAdd("m", 1, fech)

      MaxMes = Format(fech, "dd/mmm/yyyy")

    Else

       MaxMes = ""

    End If

End Function

0
August 17, 2011

thanks...I've tried these suggestions, but I think the problem is with the converting to period part.

It is not accepting 12 for some reason....I think I am missing something.


Rather than "12/6", try passing either "12/Jun" or "Dec/6", whichever is appropriate.

0
August 17, 2011

Rather than "12/6", try passing either "12/Jun" or "Dec/6", whichever is appropriate.

I can't do this because I am only giving it month/year. December 2006 is 12/06, I need it to interpret the monthe and year and then transform it to a period, suing the formula "ConvierteAPeriodo = Month(fecha) - 1 + ((Year(fecha) - 1900) * 13)".

thanks fr the help. I am still trying to find a solution

0
August 17, 2011

Here's the trouble I'm finding...

First your function is using fecha as a variant...declare it as an integer

Public Function MaxMes(fecha As Integer) As String

Next, you're assigning mes to Month(fecha) + 1

The Month function returns the month from a 'date', not a number...therefore, remove the month from your calculation, then test it:

    mes = fecha + 1

    If mes = 13 Then mes = 1

Next problem I see is you're trying to append the Year(fecha) - again, same problem - you're trying to run a date function against a digit...

Since I don't know what year you're trying to grab, I would presume just year(date), unless you can provide more information on what year you're trying to obtain:

    fech = CDate("01/" & mes & "/" & Year(date))

Finally, when performing date calculations, you should use the dateadd function.  What are you trying to subtract?  A day, a month?

This is for 1 day prior to fech

    fech =  dateadd("d", -1, fech)

0
August 17, 2011

jisalazar,

did you try   wrapping the 'fecha' with #

as in  #12/06#

0
August 17, 2011

sirbounty posted:

   Here's the trouble I'm finding...

   First your function is using fecha as a variant...declare it as an integer

   Public Function MaxMes(fecha As Integer) As String


No, sirbounty.  He's treating fecha as a Date.  If anything it should be declared as a Date datatype.


 jisalazar wrote:

    Rather than "12/6", try passing either "12/Jun" or "Dec/6", whichever is appropriate.

    I can't do this because I am only giving it month/year. December 2006 is 12/06, I need it to interpret

    the monthe and year and then transform it to a period, suing the formula

    "ConvierteAPeriodo = Month(fecha) - 1 + ((Year(fecha) - 1900) * 13)".


Ah... that indeed is the problem.  12/06 is _not_ December 2006!  It will be read as 12 June 2007, or 6 December 2007 depending on your regional settings.  "00/00" format values will be read as day and month, in one order or the other.  

 To force them to be interpreted as month and day you need to use four a digit year; 00/0000 format.  IE: use "12/2006" et cetera.

Or you could always just pass seperate month and year parameters to a function call.  That would be preferable programing practice in this case.

0
August 17, 2011

GreymanMSC ,

<Ah... that indeed is the problem.  12/06 is _not_ December 2006!  It will be read as 12 June 2007>

It will be treated/read as  12 divided 6

0
August 17, 2011

Did you find a solution?

/gustav

0
August 17, 2011

sorry I left this open...your solution helped me solve this.

I spaced out.

Joseph

0
August 17, 2011

Great. Thanks!

/gustav

Related Questions

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can&#39;t write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message &quot;Failed to Read More

Views

2k

Votes

0

Answers

2

November 08, 1998

Please help understand these notes on image processing

Can someone please help me understand these lecture notes... On the right of the page; What does &quot;normalised by one notion of the area of a pixel&quot; mean? On the first formula for A, Is that a Read More

Views

1k

Votes

0

Answers

3

February 05, 2009

Can someone please explain this paragram on the chain rule in image processing

Please see the screenshot, How is 3133030 got from 10103322? Read More

Views

1k

Votes

0

Answers

1

January 05, 2009

AD on 2003

We have created an AD Domain on Windows 2000 Server with no problems. We just created an AD Domain on Windows 2003 and we&#39;re getting some weird problems. Both of these domains are behind firewalls Read More

Views

1k

Votes

0

Answers

15

May 11, 2003

ADDT ASP Upload Error " Type mismatch: 'tNG_isFileInsideBaseFolder' "

I am trying to create a simple insert record and upload image function on an ASP page built using Adobe Dreamweaver Developer Toolkit. I have done this many times before with no problem, however, i ha Read More

Views

1k

Votes

0

Answers

0

November 02, 2008

MYSQL Select query with custom ORDER BY

Hi, is it possible to customize the order of the returned rows in mysql? Example: I have a Table with a column &quot;name&quot;, now I want to have all entries ordered by name, but I want the entries Read More

Views

1k

Votes

0

Answers

5

July 02, 2010

Folder Redirection in Server 2003

We have a Windows Server 2003 Standard Ed. Is there a way to redirect the users my documents to a folder that has already been created on the server? Read More

Views

1k

Votes

0

Answers

2

July 05, 2007

Partiton magic  version 8.0 having error 1523 while executing batch

I followed the partition magic 8.0 wizard to partition my hard disk while rebooting &#160;I came across this problem &#160;&quot;error 1523 while executing batch&quot; What can I do to solve this prob Read More

Views

1k

Votes

0

Answers

2

May 11, 2003

Please explain this paragraph in image processing (screenshot attached)

Two questions; How is the equation (10.1-15) formed? &quot;are isotropic for rotation increments of 90 degrees and 45 degrees respectively&quot; What does this mean Read More

Views

808

Votes

0

Answers

7

January 05, 2009

xp_cmdshell with net use

Hi, when I use &#39;net use \192.168.0.1\c$ password /user:username&#39; at the command prompt, it works fine. &#160;But if i try to use it in sql with &quot; exec master..xp_cmdshell &#39;net use \19 Read More

Views

755

Votes

0

Answers

5

April 11, 2003

hp ux11

i have a hp ux11 server. and i edited the /etc/resolv.conf to use my win2003 server dns server. That went fine, however, I would like to go the other way and add an entry on my win2003 dns to resolve Read More

Views

440

Votes

0

Answers

3

October 12, 2009

Using an application over a LAN

I have written an application that needs to be used on a LAN. &#160;The application setup wizard takes care of registering everything on a single PC, but how do you go about writing a setup program th Read More

Views

387

Votes

0

Answers

9

August 07, 1998

Need subnet of 12 IPs.

Please, Can someone help me to create a subnet consisting of 12 IP&#39;s. Thanks.. Read More

Views

395

Votes

0

Answers

5

April 11, 2003

Drill down in a cross-tabl report

Hello, Would some experts to show me if it is possible to do drill-up or drill-down (or both) in a cross-tab report? I would also like to know if it is possible to control what fields to be displayed/ Read More

Views

322

Votes

0

Answers

0

September 06, 2005

Using RDO, Enterprise V Professional

We would like to use RDO on our project made up of 4 developers. Do we need to have a copy of Enterprise for each developer or is it possible to use Professional for those developers not writing the R Read More

Views

377

Votes

0

Answers

6

January 07, 1998

Can't Edit IIS Metabase.xml

We do this on every windows 2003 server and never had an issue. We cannot edit the IIS Metabase.xml We have stopped IIS and made change click save, no problem but it does not take the new information. Read More

Views

376

Votes

0

Answers

0

July 05, 2007

copy directorires - HP UNIX

Let&#39;s say my source file is /A/B/C/D/file1, and mytarget directory is /E/F/G, I want the result should look like this. /E/F/G/A/B/C/D/file1 I am using rsync to copy the files older than x no of da Read More

Views

388

Votes

0

Answers

14

September 12, 2009