Date manipulation feasible for printing on cheques

[Leen]

Windows 10, Access 2016



Is it possible to manipulate a date (chqDate) with mid$, instr$, etc. to print on a cheque?
Example: Date is 07-04-2019, to be printed on cheque as 0 7 0 4 2 0 1 9

Your help would be greatly appreciated!

[Micron]

fer sure, but where is this to be done? A form control? Done via vba code? 07042019 would be a breeze. 0 7 4 2 0 1 9 is a bit more challenging.
EDIT - and of prime importance is the consistency of the character count when doing such things

[Leen]

It is a date field on a single form, then put into a query to print on a report. Yes, VB code would be great!

[Micron]

Maybe I edited before you answered, but you didn't comment on
- whether or not the date will always be the same # of characters
- where the new value is going to be "It is a date field on a single form" - that could mean the date or maybe the new value
- if you really need the spaces

Then there is
- will it always be in that format?
- can the form field be null (blank)? What should the value be if yes, and it is null?

The simple but ugly answer to your original question is

mid("07-04-2019",1,1) & " " & mid("07-04-2019",2,1) & " " & mid("07-04-2019",4,1) & " " & mid("07-04-2019",5,1) & " " & mid("07-04-2019",7,1) & " " & mid("07-04-2019",8,1)  & " " & mid("07-04-2019",9,1) & " " & mid("07-04-2019",10,1)

Seems to me that what's needed is a function to manipulate the form value and return it to your query, but I'm sorta still guessing at things.

[Leen]

Hi again, the date format will always be the same; it has to be printed on a bank cheque, which has 8 white "blocks" on it, and the "explanation" underneath, like m m d d y y y y.
No, the form field cannot be null; one doesn't write a cheque without the date on it...

[Micron]

one doesn't write a cheque without the date on it

Exactly. So you must have already taken care of ensuring the form date field is never null before a user does anything that would execute any conversion, such as running a query. Then if you put the code below in a standard module you can have a calculated query field return the conversion IF the query contains a field that contains the unconverted date. Say that field is ChequeDate. Then in query design grid, a field would have

TheDate: ConvertDate([ChequeDate])

If the unconverted date is coming from txtChequeDate on the form "frmChequeForm, the calculated query field would be

TheDate: ConvertDate(Forms!frmChequeForm.txtChequeDate)

Test by calling the function in the immediate window as ConvertDate "07-04-2019". If OK, remember to comment out the message box line before deploying. Without knowing more about the setup behind this, I can't say if you'll get only 1 date from a query or not. However, the function could return a value to a form field only once per cheque record if that's what's needed. Of course, I went with your assurance that the date would always be dash separated, contain the same character count, etc.

Hope that helps.

Public Function ConvertDate(strDate As String) As String
Dim n As Integer

strDate = Replace(strDate, "-", "")
For n = 1 To Len(strDate)
ConvertDate = ConvertDate & Mid(strDate, n, 1) & " "
Next
ConvertDate = Trim(ConvertDate)

MsgBox ConvertDate '<<< comment out this line when working
End Function

[CJ_London]

passing a date to a string will fall foul of the US date format issue

i.e. 7th April in UK
?convertdate(#07/04/2019#)
0 4 / 0 7 / 2 0


so either use the format function before calling the function to correct this or modify

Public Function ConvertDate(myDate As Date) As String
Dim n As Integer
Dim strDate As String


strDate = Format(myDate, "ddmmyyyy")
For n = 1 To Len(strDate)
    ConvertDate = ConvertDate & Mid(strDate, n, 1) & " "
Next
ConvertDate = Trim(ConvertDate)


Debug.Print ConvertDate  '<<< comment out this line when working
End Function

?convertdate(#07/04/2019#)
0 4 0 7 2 0 1 9

part of the problem with filling boxes on forms is the spacing - it may be on your report you will need 8 separate controls so you can place them exactly. in which case you need a function to return a single character - one to each of 8 controls

Public Function ConvertDate2(myDate As Date, pos as integer) As String

ConvertDate2=mid(format(myDate,"ddmmyyyy"),pos,1)

Debug.Print ConvertDate2  '<<< comment out this line when working
End Function

?convertdate2(#07/04/2019#,1)
0
?convertdate2(#07/04/2019#,2)
4
?convertdate2(#07/04/2019#,3)
0
etc

[Leen]

passing a date to a string will fall foul of the US date format issue

i.e. 7th April in UK
?convertdate(#07/04/2019#)
0 4 / 0 7 / 2 0


so either use the format function before calling the function to correct this or modify

Public Function ConvertDate(myDate As Date) As String
Dim n As Integer
Dim strDate As String


strDate = Format(myDate, "ddmmyyyy")
For n = 1 To Len(strDate)
    ConvertDate = ConvertDate & Mid(strDate, n, 1) & " "
Next
ConvertDate = Trim(ConvertDate)


Debug.Print ConvertDate  '<<< comment out this line when working
End Function

?convertdate(#07/04/2019#)
0 4 0 7 2 0 1 9

part of the problem with filling boxes on forms is the spacing - it may be on your report you will need 8 separate controls so you can place them exactly. in which case you need a function to return a single character - one to each of 8 controls

Public Function ConvertDate2(myDate As Date, pos as integer) As String

ConvertDate2=mid(format(myDate,"ddmmyyyy"),pos,1)

Debug.Print ConvertDate2  '<<< comment out this line when working
End Function

?convertdate2(#07/04/2019#,1)
0
?convertdate2(#07/04/2019#,2)
4
?convertdate2(#07/04/2019#,3)
0
etc

Thank you both for your extensive work!
However, Ajax, what I find confusing is that the date I am talking about as an example is 07-04-2019, being July 4, 2019,
which could not be ddmmyyyy.

Also about what I need to do goes way over my head. Could you please tell me what to put where, i.e. the table, form, report, query.
That would make things a lot easier for me; I call myself an "advanced" beginner, notice the quotation marks.

Thanks again for your work!

[Micron]

In spite of the name of my function, I just presumed the input value was a string because 1) of the date separator, and 2) because it might be coming from a form control.
My questions were not being answered completely so I gave up trying to understand exactly what was going on. I figured, get to a point where it's tested and/or placed where it needs to be and see what happens. As far as that goes, it was explained in my last post. If you are saying, for example, that you don't know what a standard module or calculated query field is, Google it - it's a great way to learn this stuff. Maybe best of all is how it presents LOTS of info on a subject rather than one or two opinions or answers from here, and often leads to related learning.

[CJ_London]

what I find confusing is that the date I am talking about as an example is 07-04-2019, being July 4, 2019

if you are in a country which has a default date style per the US of mm/dd/yyyy then you don't need the format - so sounds like you are in the US

Could you please tell me what to put where, i.e. the table, form, report, query.

copy the function to a module and call it in your query

e.g. chkDate:ConvertDate(date())

make sure you bone up on dates - they are stored as numbers, not text

[Leen]

if you are in a country which has a default date style per the US of mm/dd/yyyy then you don't need the format - so sounds like you are in the US

copy the function to a module and call it in your query

e.g. chkDate:ConvertDate(date())

make sure you bone up on dates - they are stored as numbers, not text

Thanks Ajax!
I am going to check it out. b.t.w. I live in Canada

[Leen]

Thanks Ajax!
I am going to check it out. b.t.w. I live in Canada

Hey Ajax,

I put your function in a module and no errors.
However, I tried to call ConvertDate in the query (chqTbl-Qry) in many different ways, but no results so far.
What is worse, in some ways even the Cheque Writer Main form does not open after trying the call.
Could you help me out here?

Query: Criteria in chqTbl-Qry, under heading chqDate field.

P.S. For yours and other people's info: I am really good in developing layouts, but not terribly technical...

Thanks!

[CJ_London]

However, I tried to call ConvertDate in the query (chqTbl-Qry) in many different ways, but no results so far.

to be clear, you have put the function in a standard module (not a form or class module) - and in the query you are passing to the function a date field, not a string (which micron was assuming).

with regards the code,
>have you got 'option explicit' at the top of the module, below 'option compare database'?
>have you compiled the code and don't get any bugs?

can you clarify what 'no results' means - you get an error (if so what is the error description?), a blank field? something else?

suggest post the sql to your query in its entirety, to see if something else is going on

[Leen]

to be clear, you have put the function in a standard module (not a form or class module) - and in the query you are passing to the function a date field, not a string (which micron was assuming).

with regards the code,
>have you got 'option explicit' at the top of the module, below 'option compare database'?
>have you compiled the code and don't get any bugs?

can you clarify what 'no results' means - you get an error (if so what is the error description?), a blank field? something else?

suggest post the sql to your query in its entirety, to see if something else is going on

I checked the following:

ChequeWriter Form will not start up, because "Undefined Function, ConvertDate in Expression"
I put ConvertDate in a Module, but there was no Option Explicit, which I then added.
The no results refer to the Form not starting up, or seeing a "#" in the chqDate field. When I call CovertDate in the query, mostly it is accepted, until I run it.
The Print Preview button does now not respond to anything.

I run regularly the "Compact and Repair" button, and now it gives the aforementioned error, but usually it seems to be fine with the software.
I looked into compiling the database, but even after some research am not sure what to do.
Lastly, there is no SQL choice in the View dropdown, only: Form View, Datasheet Vuew, Layout View, Design view. Would there be another way to access it?

Thank you for your patience!

[CJ_London]

I run regularly the "Compact and Repair" button

that is not the same as compiling - in the vba window, go to Debug>Compile. Do this before continuing

looked into compiling the database, but even after some research am

not sure what to do.

see above

ChequeWriter Form will not start up, because "Undefined Function, ConvertDate in Expression"

a) check spelling, b) ensure module has different name from function c) ensure function either has nothing or Public at the start (nothing is ok because Public is the default state) d) if being used in a form or report, post a screenshot of the controlsource for the control in question

Lastly, there is no SQL choice in the View dropdown, only: Form View, Datasheet Vuew, Layout View, Design view. Would there be another way to access it?

you really do need to spend a bit of time to learn about access. SQL is used in queries, queries are used (among other things) as recordsources for forms and reports and rowsources for combo's and listboxes. So your form has a recordsource - this will be a table (bad idea) a query or the sql. But from the sound of it you are not applying the function in a query anyway - however that is where I would expect you to use it - in the query which is the recordsource to the report you run to print the cheques